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

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.

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

• Install SQL 2008 on the new box, using the same file structure as the old box for MDF and LDF files.

• Take backups of all databases on the old box.

• Restore master from the old box to the new box having the SQL started in single user mode. Restore master method

• 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.

• 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.

StackExchange Database Administrators Q#1880, answer score: 10