Saving Time with Differential Backups

Recently I've been migrating a lot of databases from stand-alone physical servers into a PolyServe environment. Since this obviously requires a downtime, we have to schedule the moves with limited amounts of time. To add to the issues at night, the NetApp filers are busy so copies are slower.

In order to speed up the process and reduce the application downtime, I decided to use differential backups - which I will admit to not using that often.

If my downtime starts at 11PM, I will take a Full database backup earlier in the evening with SSMS. I backed the database up to the remote server that was to become the new SQL Server (\\server\path\db.bak) in order to avoid having to copy the backup file later.

After the full backup was completed, I restored it to the new server and made sure all my logins were moved over with sp_help_revlogin. Make sure when you restore the full backup that you choose the RESTORE WITH NO RECOVERY option, or you will not be able to apply the differential backup. Also, verify that the directory structure you are restoring the files to is correct. It will try to restore to the directory structure used by the old server.

Once the downtime started, I made sure all user connections to the database were gone and kicked off my differential backup. My full backup was nearly 20GB, but my differential was less than 200MB, which I also backed up to the new database server.

Once the differential backup was completed, I restored this database (using the Restore Database functionality in SSMS) as well. This time select the option to Restore with Recovery if you do not have any transaction logs to restore. If you shut down database access and took a differential like I did, there will be no transaction logs to worry about. Once you Restore With Recovery, the database will be available for use immediately after the restore is complete.

At first I wasn't sure if I should Restore Log or Restore Database, but Restore Database seemed like the best option and it was the right one. For some reason I had to fix the paths to my databases again, because the backup was using the directory structure from the old server here as well. I hoped it would be smart enough to know what to do on its own, but wasn't.

I did not specify OVERWRITE. The restore initially was quick but then hung on 90% for a while. It finally finished up and my database was ready to go. My downtime was 15 minutes - the time it took to take the differential and restore it on the other server. For a savings of at least 30 to 40 minutes.

I'd rather put the time in up front and reduce downtime with an added bonus of not having to stay up as late waiting on it. I used the time I saved to write this article as a quick reference for anyone else who is looking for the same information.