Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I work in a development shop where we primary use SQL Server for our day-to-day development and testing.

Sometimes issues occur on someone's machine, often a labtop, that I would like to reproduce on my machine. I therefore want to transfer their databases to my machine. This transferring is often offline where the labtop is elsewhere, so we dump databases, FTP them in and restore them, so online transfer mechanisms aren't usually available. Usually there are 5+ dump files to restore.

The current process is to use backup database + restore database and passing the dump files around. This works ok but is a real pain because of the physical names stored in the backups. When doing the restore I have to do with move to map the logical files into the backup file to the physical location of the mdf/ldf files on my machine.

Given SQL Server knows the default location to create these files, as create database works like this, is there any way I can come up with a smarter way of moving these dbs from one instance to another? I would be quite happy if db names were exactly the same (if that is scrappable from the dump files) and physical location would be inferred from the target instances default.

NOTE: As an aside the Management Studio restore option (GUI) seems to be clever enough to manage this without having to detail the physical locations manually.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

The default folders for a SQL instance is stored in the server's registry in

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\[your specific instance ID]\MSSQLServer

You can use xp_regread to pull this value and then use that in your restore scripts. Here's an example:

DECLARE @DataDirectory VARCHAR(255)
DECLARE @LogDirectory VARCHAR(255)

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer',
  @value_name='DefaultData',
  @DataDirectory=@DataDirectory OUTPUT

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer',
  @value_name='DefaultLog',
  @LogDirectory=@LogDirectory OUTPUT

RESTORE DATABASE MyDB
FROM DISK = 'X:\SQLBackups\MyDB.bak'
WITH MOVE 'MyDB_Data' TO @DataDirectory + 'MyDB.mdf',
MOVE 'MyDB_Log' TO @LogDirectory + 'MyDB.ldf';

You would replace MSSQL10_50.MSSQLSERVER with the instance ID of the server instance on which you are performing the restore.

Something easier is to use xp_instance_regread which automatically discerns the instance ID of the server instance you're working on. So the above would be rewritten as such:

DECLARE @DataDirectory nvarchar(255)
DECLARE @LogDirectory nvarchar(255) 

EXEC master..xp_instance_regread
    @rootkey=N'HKEY_LOCAL_MACHINE',
    @key=N'Software\Microsoft\Microsoft SQL Server\MSSQLServer',
    @value=N'DefaultData', 
    @DataDirectory=@DataDirectory OUTPUT

EXEC master..xp_instance_regread
    @rootkey=N'HKEY_LOCAL_MACHINE',
    @key=N'Software\Microsoft\Microsoft SQL Server\MSSQLServer',
    @value=N'DefaultLog', 
    @LogDirectory=@LogDirectory OUTPUT

RESTORE DATABASE MyDB
FROM DISK = 'X:\SQLBackups\MyDB.bak'
WITH MOVE 'MyDB_Data' TO @DataDirectory + 'MyDB.mdf',
MOVE 'MyDB_Log' TO @LogDirectory + 'MyDB.ldf';

(Note: I'm working with SQL Server 2008 R2 here)

Regarding moving the databases around, you can detach the database from the source then copy the physical data and log files to your target location and then reattach them there (as well as back on the source machine). I prefer the backup / restore process personally, especially if you are in a position where you need ftp to do the file transfer.

You could even set up replication, mirroring or log shipping but I think that in your case this would be overkill. I'd stick with the backup / restore method.

share|improve this answer
    
Thanks this should get me part of the way there, esp being able to access the default directories. –  Mike Q Jan 6 '12 at 17:30
    
@Mike I added my thoughts regarding the overall process to my answer. –  squillman Jan 6 '12 at 18:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.