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.

  • 1
    You can find some ideas in this previous question. But the previous answers are pretty explanatory. I think that the best idea would be to establish a mirroring and break it after, or a Log shipping (can do only from wizard) for all dbs and just restore further the necessary log backups, but if you say you're not a DBA, then probably the backups method is better.
    – Marian
    Commented Mar 24, 2011 at 15:10

2 Answers 2


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.

  • 3
    Don't forget copying any data changes made after taking the backups. You can take a diff backup or tlog backup and apply them to the new server after it's up, but before you switch over. Commented Mar 24, 2011 at 13:16
  • good point, i forgot to mention that as well. perhaps i should include a db mirror configuration, which could be broken afterwards? Commented Mar 24, 2011 at 13:27
  • 1
    I would imagine someone already has an extensive article on the different ways to migrate servers. Also, here's an article on transferring logins to a new server: support.microsoft.com/kb/246133 Commented Mar 24, 2011 at 13:39

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
  4. Setup the mirroring on the mirror, pointing at the primary:

    EXEC sys.sp_dbmmonitoraddmonitoring
  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:


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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