views:

199

answers:

2

I'm trying to write a script which automatically restores a database backup. I know I can use the following RESTORE command:

RESTORE DATABASE [DBRestoredName] 
FROM  DISK = N'C:\path\to\backup.bak' 
WITH  FILE = 1,  
MOVE N'DBNAME' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DBNAME.mdf',  
MOVE N'DBNAME_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DBNAME.ldf',  
NOUNLOAD

The problem with this is I want to be able to determine the SQL server's data location (i.e. the TO path) at run-time so the restored database is placed consistently alongside other databases on this server.

The database being restored won't exist on the server it's being restored to and I require the MOVE statements as the source server is likely to be SQL server 2005 and the target is 2008 therefore the file paths included in the backup file are not desirable.

So what ways could I determine the SQL data location programmatically?

+1  A: 

You can query this from the sys.database_files view in the database you wish to restore alongside physically. You will have to strip off the file name from the end of the physical_name column.

select * from sys.database_files
doug_w
but which entry do you use? in my case, I have my master/msdb/model databases in a different directory from my user databases.....
marc_s
"consistently alongside other databases" is what I went off of from the question. He did not necessarily ask how to find the default data directory as marc_s specified the solution to. Really sound like 2 different problems IMO. In several instances that I administer we must have multiple drives that host different databases for performance reasons making the default data directory a bad choice in most cases for this situation.
doug_w
sorry for the confusion. It was badly phrased and while I was really after the default data directory this answer is just as valid so I upvoted this too. Thanks
Dolbz
+1  A: 

The only viable solution I found is inspecting the registry from your T-SQL code:

DECLARE @filepath NVARCHAR(260)

EXEC master.dbo.xp_instance_regread 
        N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', 
        N'DefaultData', 
        @filepath output, 'no_output' 

SELECT @filepath as 'Your default data directory'

I could have sworn that data path would be stored somewhere in a SERVERPROPERTY or a Dynamic Management View (DMV) - but no luck ......

marc_s