How to specify the MSSQL.1 DATA folder in SQL Express?

Graham Tree

How do you determine the data folder during the SQL2005 Express install

No matter what folder I start with it gets appended with MSSQL.1 or MSSQL.2

If another vendor has installed a different instance of SQL2005 how do I know whether mine is .1 or .2

Basically I need to know the full folder in order to copy our MDF/LDF files into so we can attach them.

Or is there another way

Thanks.

Graham

Andrea Montanari

hi Graham,

you can query the sys.database_file catalog view to have a reference to the master database of the instance you are running on..

so, with a connectio to the master database you can execute

SELECT physical_name FROM sys.database_files WHERE physical_name LIKE '%.mdf';
--<------
physical_name
--------------------------------------------------------------------
C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\master.mdf

so you can have a clue on where your \Data\ folder is without querying the registry..

regards

Jens K. Suessmeyer

As you did not mention where you want to use it (either programmatically or adhoc), youc could also use the programmtic approach using SMO and determining the database folder through this API, but if you did not got in touch with SMO yet, use the method of Andrea to query the master database.

Jens K. Suessmeyer

---
http://www.sqlserver2005.de
---

Jon A.

FYI, you don't HAVE to copy the MDF/LDF to the default data directory for the instance. Personally, I'd go with a completely different convention myself as I'm not fond of hiding data files deep within the dark recesses of the Program Files directory. Something like C:\SQLData\InstanceName is probably what I'd do.

Graham Tree

Thanks Jon.

We do that already.

My data is installed in x:\MSSQL2005\InstanceName\

MSSQL than adds:

x:\MSSQL2005\InstanceName\MSSQL.1\MSSQL\DATA

but if someoneelse has installed a different app I would get

x:\MSSQL2005\InstanceName\MSSQL.2\MSSQL\DATA

The problem is I don't have any control over the last part.

Graham Tree

Thanks Andrea,

I'll try to figure out how to do this during the install.

Using SQLCMD.exe to execute the script where could I pick up the results

Can I pipe it into a text file

Andrea Montanari

hi,

SqlCMD accepts dynamic variable replacement providing variable's value even from the outside..

say you have a script file to be executed like

[file.sql]
DECLARE @path varchar(128);
SELECT @path = '$(path)';
SELECT @path AS [your output path];
[/file.sql]

you can call SqlCmd providing the path variable value to be used inside the Transact-SQL code of the file..

so you can call SqlCMD like:

c:\..\>SqlCMD -S.\SQLExpress -E -v path = "c:\myfolder\" -i c:\file.sql >c:\output.txt

and "c:\myfolder\" will be used inside the script as the $(path) variable's value..

at install time, you'll probably call SqlCMD interactively inside your installer package, and as long as you have the folder you like to use, you can pass it to SqlCMD as well very simply..

just bear in mind this only works locally to the machine you installed SQLExpress on, as, for instance, you can not use this scenario from remote connections, as SqlCMD could not be installed on that remote machines..

regards

Mike Wachal - MSFT

FYI - You can not control the addition of the MSSQL.# folder on the installation path. This is refered to as the Instance ID and is the mechanism that we use to enumerate individual instances of SQL Server. All Instances specific files and folders will reside underneath the Instance ID folder location.

During installation we create the default Data folder to this location. After installation you can change the default data folder (and log folder too) by changing the property. You can change the Data and Log folder property in the Server Properties using SSMS. You can probably change it in T-SQL, but I don't know the setting off the top of my head. I'm sure a search of BOL will yield results for you.

Regards,

Mike