A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.
Several levels of indirection here:
I want to copy a SQL Server .MDF database file from my local workstation to a SQL server to replace an existing SQL Server database. I know the local path to the file, and I know the ODBC DSN to the SQL Server database, but I don't know the UNC path to map to the SQL Server's local directory where the data file will end up.
Assuming I have permission to copy a file to the ultimate destination, is there some Windows function that will determine the local directory on the SQL server given the SQL Server database name from my local machine's ODBC alias?
Is this what you're looking for?:
The default location of the SQL Server data files is not usually shared out on the network. Therefore, there is no guarantee that it will be accessible via an UNC path.
Will you be running this code on the machine that's actually running the SQL Server?
Also, have you considered that you would have to:
1) Stop the database.
2) Detach the MDF file
before you copy your replacement file, and:
3) Attach your new file
4) Connect and test the database
after your copy is complete?
Thursday, September 15, 2005
"Therefore, there is no guarantee that it will be accessible via an UNC path."
In my original post I said, "Assuming I have permission ..." So it will be accessible.
"Will you be running this code on the machine that's actually running the SQL Server?"
No. If I was running the code on the SQL Server it would be irrelevant - the utility would be able to use local paths.
"Also, have you considered that you would have to:"
Herb was onto an idea, but unfortunately it was the reverse of what we need.
Karl -- I'm curious if you've found a solution for this.
I think one way might be to make a stored procedure on server and use the xp_cmdshell function to get the data from a shell command like the wnetgetuniversalname or the undocument dos command 'truename' to return the unc. Maybe you'd need to use combination of more than one shell command, I don't know.
But seems like this xp_cmdshell function in SQL Server opens up a lot of possiblities:
When I said "There is no guarantee", I did not mean permissions. I meant that the directory is not usually shared on the SQL Server machine. If it is not shared, the question of permissions does not arise.
In any case, let us assume that the SQL Server data directory *is* shared. Even in this case, only the server would be able to do the database to path mapping. So, a possible solution is to write a stored procedure on the server, which does the following:
1) Find the physical location of the file(s) for your database. This can be achieved by SELECT filename FROM sysfiles.
2) Use these filenames(which would, of course, have local paths) to get the name of the file share on which they reside, perhaps using Herb's method. This would be tricky. You could create a COM component which you could call from the stored procedure, or you could write the whole thing as an Extended Stored Procedure.
3) Return the share name to your app, which then proceeds to copy the new file, stop the current database, detatch it, etc.
xp_cmdshell does open up a lot of possibilities. Unfortunately, it's also a potential security hole, as it allows users to run arbitrary commands in the context of either the SQL Server service account, or the SQL Agent proxy account, either of which may be dangerous. In general, it should be avoided.
If this were not so, the following command (available only to Admin-level users) would be called via xp_cmdshell for step 2 of the solution I outlined above.
WMIC SHARE WHERE Path='C:\Whatever\path' GET Name
This would return the share name for the physical path.
Also, the undocumented TRUENAME DOS command is not available on NT-based operating systems.
Raj -- Thanks for clarification. I was assuming that there was some way to assign permissions so that xp_cmdshell could be used only within context of another stored procedure, and not otherwise. But I guess if you open up xp_cmdshell for one thing then it's going to be open for all. Is that right, there's no good way to limit it's use?
Well, there is some level of protection, in that by default, only logins belonging to the sysadmin role can execute xp_cmdshell. It runs as the SQL Server service account.
It is possible to explicitly give execute rights for xp_cmdshell to other logins. In this case, it runs as the SQL Server Agent proxy account. By limiting the rights of this account, we can reduce the attach surface.
This topic is archived. No further replies will be accepted.Other recent topics
Powered by FogBugz