Create a SQL Server database on a remote machine mdf file path issue

Question

I'm trying to create a MS Sql Server database on a database instance running on a remote machine. When I'm doing so I need to be able to specify the path to the database (.mdf) file. If I try to create a database in a folder which doesn't exist, SQL Server will just fail (wouldn't it be nice if it created the folder structure automatically).

Is there any way that I can create the folder path on the target machine in SQL before I try to create the database, or at least to determine what the default folder is for new databases in which I could safely create the new database file?

Answer 1

If you have appropriate permissions, and xp_cmdshell is enabled, you can:

EXEC xp_cmdshell 'md "<path>"';
--...repeat for each node in the path

If cmdshell is disabled, again assuming appropriate permissions, you can enable it temporarily using sp_configure:

http://aspfaq.com/sql2005/show.asp?id=29

Don't forget to set it back!

Otherwise why can't you let the engine place the database files in their default location? If you are using a drive other than C:, you'll also need to verify that the drive you specify even exists, and shouldn't you check with the user that it is okay for you to put these files elsewhere? If you choose some arbitrary location they might not know to check there for active SQL Server files.

You can check the default path by using xp_regread (undocumented, unsupported)... these are in the registry as keys DefaultDataDir and DefaultLogDir for the default instance under:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer

If it's not the default instance, check this article:

http://foxtricks.blogspot.com/2009/06/how-to-determine-default-database-path.html

Are you doing this just so that you can name your MDF/LDF files the way you want to, instead of dbname-data, dbname-log? If so, why? Have you written scripts that depend on the physical name of the file? Really curious as to the motivation behind this.