3

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.

Mike Q
  • 197
  • 7

1 Answers1

2

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.

squillman
  • 37,883
  • 12
  • 92
  • 146