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?