Showing results for 
Show  only  | Search instead for 
Did you mean: 
Level 7

SQL Server Database creation on remote machine

Please explain how I go about creating a SQL Server Database on a remote machine. Specifically, how do I get the path (FILENAME entry) to the SQL Server folder on the remote machine, in order to run a Database creation command in a SQL Script.

I am using the standard SQL Login dialog to specify the machine which is running SQL Server, then in the SQL Scripts view I have a script which contains the following line:

CREATE DATABASE [DBName] ON (NAME = N'DBName', FILENAME = N'DBName.mdf' , SIZE = 4, FILEGROWTH = 10%) LOG ON (NAME = N'DBName_log', FILENAME = N'DBName_log.ldf' , FILEGROWTH = 10%)

In the text replacement section I have entries which adjust the .mdf and .ldf entries eg:

Find What: FILENAME = N'DBName.mdf'
Replace With: FILENAME = N'[SQL_DATA_PATH]\DBName.mdf'

[SQL_DATA_PATH] is typically C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data OR C:\Program Files\Microsoft SQL Server\MSSQL\Data

I can use a system search for this on the local machine, but how do I find out what it is if the user has opted to use a remote machine?

I have no problem getting it to work if the SQL Server is on the local machine and I use a system search to set [SQL_DATA_PATH].

It also works if [SQL_DATA_PATH] gets hard coded to one of the above choices - assuming it is correct for the machine in question, but I dont know what that path is going to be, or what variety of SQL Server it is going to be and I only know what machine it is going to be after the SQL Login dialog.

I can't understand how the SQL Login dialog can verify that you have a valid SQL Server connection to a remote machine, but it doesn't get the Path details at the same time. Is there some property I have missed or is there some SQL Script setting which effectively says 'Use default path'?

Thanks in advance.


Basic MSI,
SQL Server 2000/SQL Server 2005/MSDE/SQL Server Express
Windows NT/2K/2K3/XP/Vista
Labels (1)
0 Kudos
2 Replies
Level 3

Is there a reason that you want to control the path and exact filenames used on the remote SQL server? Do you need to control the initial size or growth?

If not, you can let SQL Server create the DBName.mdf and DBName_log.ldf in the default folder for the instance that you are connecting to.
To do this just use:

0 Kudos
Level 7

Thank you Kelly, that is exactly what I needed.

0 Kudos