Restore database to default location on anohter server?

I have a database called "test_db" that has been backed up with the
command:
BACKUP DATABASE test_db TO DISK = 'c:\temp\test_db.bak'
I want to restore it to another Sql Server and call it
'Test_Restored_DB', as well as save it as a different physical file
name. So I copy the file over to the new server into the folder
c:\temp\ and then execute the command:
RESTORE DATABASE Test_Restored_DB
FROM DISK = 'c:\temp\test_db.bak'
WITH MOVE 'test_db' TO 'c:\AnotherTempDirectory\Test_Restored_DB.mdf',
MOVE 'Test_db_log' TO 'c:\marcustemp\Test_Restored_DB.ldf'
This works fine. But what I really want to do is restore the database
to the default location on Sql Server 2005, i.e. typically "C:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" without actually
hard-coding this path in. This is because I am assuming that some Sql
Servers may be configured differently and have their default data
directory located in another folder (I have no control on the
configuration of various sql servers where this will be run). Is there
a way to restore a database to another server, give it a new name,
change its physical file name, and have it placed in the default data
directory without specifying the data directory? Or perhaps there is a
stored procedure (not xp_cmdshell) or some other t-sql code that will
tell me where the default data directory is located?
Thanks for any feedback,
MarcusHi,
I have never tried that but this is what SQL Server does in the background
to find these locations
declare @.SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData',
@.SmoDefaultFile OUTPUT
print @.SmoDefaultFile
declare @.SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @.SmoDefaultLog
OUTPUT
print @.SmoDefaultLog
declare @.SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @.SmoRoot OUTPUT
print @.SmoRoot
Ben Nevarez, MCDBA, OCP
Database Administrator
"Marcus" wrote:
> I have a database called "test_db" that has been backed up with the
> command:
> BACKUP DATABASE test_db TO DISK = 'c:\temp\test_db.bak'
> I want to restore it to another Sql Server and call it
> 'Test_Restored_DB', as well as save it as a different physical file
> name. So I copy the file over to the new server into the folder
> c:\temp\ and then execute the command:
> RESTORE DATABASE Test_Restored_DB
> FROM DISK = 'c:\temp\test_db.bak'
> WITH MOVE 'test_db' TO 'c:\AnotherTempDirectory\Test_Restored_DB.mdf',
> MOVE 'Test_db_log' TO 'c:\marcustemp\Test_Restored_DB.ldf'
> This works fine. But what I really want to do is restore the database
> to the default location on Sql Server 2005, i.e. typically "C:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" without actually
> hard-coding this path in. This is because I am assuming that some Sql
> Servers may be configured differently and have their default data
> directory located in another folder (I have no control on the
> configuration of various sql servers where this will be run). Is there
> a way to restore a database to another server, give it a new name,
> change its physical file name, and have it placed in the default data
> directory without specifying the data directory? Or perhaps there is a
> stored procedure (not xp_cmdshell) or some other t-sql code that will
> tell me where the default data directory is located?
> Thanks for any feedback,
> Marcus
>|||Excellent, thanks, Ben! With a little bit more googling of
xp_instance_regread lead me to this page which more stuff that I could
use:
http://www.sqldev.net/misc/SQLLocationFunctions.htm
Cheers,
Marcus
Ben Nevarez wrote:
> Hi,
> I have never tried that but this is what SQL Server does in the background
> to find these locations
> declare @.SmoDefaultFile nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData',
> @.SmoDefaultFile OUTPUT
> print @.SmoDefaultFile
> declare @.SmoDefaultLog nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @.SmoDefaultLog
> OUTPUT
> print @.SmoDefaultLog
> declare @.SmoRoot nvarchar(512)
> exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
> N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @.SmoRoot OUTPUT
> print @.SmoRoot
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Marcus" wrote:
> > I have a database called "test_db" that has been backed up with the
> > command:
> >
> > BACKUP DATABASE test_db TO DISK = 'c:\temp\test_db.bak'
> >
> > I want to restore it to another Sql Server and call it
> > 'Test_Restored_DB', as well as save it as a different physical file
> > name. So I copy the file over to the new server into the folder
> > c:\temp\ and then execute the command:
> >
> > RESTORE DATABASE Test_Restored_DB
> > FROM DISK = 'c:\temp\test_db.bak'
> > WITH MOVE 'test_db' TO 'c:\AnotherTempDirectory\Test_Restored_DB.mdf',
> > MOVE 'Test_db_log' TO 'c:\marcustemp\Test_Restored_DB.ldf'
> >
> > This works fine. But what I really want to do is restore the database
> > to the default location on Sql Server 2005, i.e. typically "C:\Program
> > Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" without actually
> > hard-coding this path in. This is because I am assuming that some Sql
> > Servers may be configured differently and have their default data
> > directory located in another folder (I have no control on the
> > configuration of various sql servers where this will be run). Is there
> > a way to restore a database to another server, give it a new name,
> > change its physical file name, and have it placed in the default data
> > directory without specifying the data directory? Or perhaps there is a
> > stored procedure (not xp_cmdshell) or some other t-sql code that will
> > tell me where the default data directory is located?
> >
> > Thanks for any feedback,
> > Marcus
> >
> >sql