SQL Server Data Refresh

Question

I need to be able to restore around 6TB of data from one MS SQL 2008 instance into another SQL Instance. DBA's and backup folk are estimating between 36 and 48 hours for the restore. I can't afford this amount of down time on the target system.

Would it be possible to mount additional drives on the target SQL server, restore the data files to the new drives and then simply change the location of the data files in the DB once the restore is complete?

This way the only down time is getting the drives mounted and switching data file location.

• Charlieface: Yes, you could detach and reattach the .mdf files directly (not the backup .bak files), but make sure it's the exact same version of SQL Server. I note that 2008 is already 2 years out of support
• David Browne - Microsoft: You can attach a user database to a later version of SQL Server. They will be upgraded automatically. It's only system databases that you have to be on exactly the same patch level form.
• Stephen Morris - Mo64: If you have a network connection between the 2 servers you might want to consider backing up the db from source, restoring but not recovering to the target and then using log shipping to synchronise the DBs to apply the changes from the transaction log backups to the target - in this way you can reduce the down time to the time to copy & restore the last transaction log backup + recovery.

Answer 1

Would it be possible to mount additional drives on the target SQL server, restore the data files to the new drives and then simply change the location of the data files in the DB once the restore is complete?

Yes, if the two databases on the target server have the same file layout. A simpler and safer procedure is to restore the database with a different name, and then rename the databases to swap.

If you can avoid detaching a databse, setting it offline or shutting down SQL Server you have extra protection against losing data.