A Non-DBA Asks: How to Painlessly Copy/Move a SQL Server instance to another Server via Backup/Restore?

Question

We've just got a new server so I need to copy the existing SQL Server 2008 instance over from the old db box.

I normally do this by copying .mdf and log files over and attaching them but I'm not able to do take the dbs off line as they're used 24/7 so I've backed up the databases and restored them on the new machine. However I've ended up with a few issues one of them being related to orphaned users. So what I'm looking for is a fail-safe process to move everything across with minimal fuss/bother/headache. I'm a .Net dev with a fair amount of SQL under my belt but the inner workings of SQL Server are largely a mystery to me and I find the MS documentation painful to trawl.

Please help.

Answer 1

If you cannot take the databases offline, tne you need to do backup/restore. I would suggest the following:

1. Install SQL 2008 on the new box, using the same file structure as the old box for MDF and LDF files.
2. Take backups of all databases on the old box.
3. Restore master from the old box to the new box having the SQL started in single user mode. Restore master method
4. Restore each database from the old box to the new box with NORECOVERY option in order to be able to apply future Diff or T-log backups.
5. Restore msdb from the old box to the new box.

If you build and configure the new box to look exactly like the old one, then you will have minimal fuss.

I have no idea how big your databases are, so this method could take a long time. The easiest thing to do would be to shut down the old box and copy the MDF and LDF files and then (after restoring master) you would only need to restart SQL to have your databases online. But you stated that is not an option, as the databases are not allowed to be offline.

Answer 2

You mention 24/7 operation, so the best way to do this is to create a database mirror on the new system, then you can simply switch over with minimum - perhaps zero, depending on how your application is structured - downtime using a client-side redirect. The basic steps are:

1. Make sure your primary database is running in FULL recovery mode.
2. Backup the primary and restore it on the mirror WITH NORECOVERY

3. Create "endpoints" on both servers, and ensure connectivity (e.g. firewall rules), setting the port and IP address correctly) e.g:

   CREATE ENDPOINT endpoint1
   STATE=STARTED AS TCP(LISTENER_PORT = 5222, LISTENER_IP = 192.168.1.5) 
   FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)
   

4. Setup the mirroring on the mirror, pointing at the primary:

   ALTER DATABASE GaiusMirrorDB SET PARTNER = 'TCP://192.168.1.5:5222'
   go
   EXEC sys.sp_dbmmonitoraddmonitoring
   go
   

5. And on the primary, pointing at the mirror (just a different database name and IP address.

6. Then, when the time comes, just switch over on the primary to the mirror:

   ALTER DATABASE GaiusDB SET PARTNER FAILOVER
   GO
   

Note: I am assuming that these two servers are on the same network, so it will be OK to operate in synchronous mode. If this is over a WAN link, use async mode.