Get started Bring yourself up to speed with our introductory content.

SQL Server backup best practices

Backup compression and using differential backups are just two of the important SQL Server backup best practices described in this tip.

Doing database backups is one of the most important tasks of a database administrator. A seasoned DBA should be able to quickly configure any server for the most suitable database strategy. But many companies have "accidental" DBAs or sometimes even non-DBA employees in charge of database backups. Over the years I have seen many incorrectly configured backup plans that were leaving the server exposed to data loss. This article's SQL Server backup best practices will make sure that you protect your data properly and that you can also recover a database from a backup if needed.

The first of the SQL Server backup best practices is to remember this: You can never have enough backups. Many things can go wrong that might prevent getting the latest backup done: running out of space, disk failures, network disruptions, backup job hanging and so on. Sometimes it's tempting to think that you do not need a backup. For example, with database mirroring you always have a copy of your database on another server. This fact might get you more relaxed when it comes to transactional or even full backups, but, as I discovered recently, you may need your backups after all. We had a drive failure on the mirror server and somehow the Microsoft Distributed Transaction Coordinator (MSDTC) had a hiccup that corrupted the primary database and put it in SUSPECT status. We were not able to bring the database online and needed to restore from backups. So even when mirroring the database, you cannot rely purely on the mirroring mechanism for database protection.

Many companies use a combination of a daily full backup and periodic transaction log backups. This strategy will get you the database recovered up to the last successful transaction log backup, but it might take a while. Let’s say you do a full backup at midnight and a transaction log every 10 minutes. If your database needs to be recovered after a crash at 10:05 p.m., you need to restore the full backup and 120 log backups. You cannot fully rely on SQL Server Management Studio to be able to generate a restore script for all those backups. Why? That only works if the database where the backup history is stored remains intact. But if you lost the entire server and you are rebuilding it, you will have to restore 120 log backups by hand. This is where differential backups come in handy.

A differential backup has all the changes since the last full backup. So in the above scenario, let's say we implemented a differential backup to execute every three hours starting at 3 a.m. and ending at 9 p.m. To recover the database at 10 p.m., you would have to restore the full backup from midnight, the most recent differential backup (9 p.m. in this case) and then the last six or seven log backups taken between 9 p.m. and 10 p.m. As you can see, your recovery time gets significantly reduced. Differential backups in most cases complete very quickly and the size is manageable. In most cases, you do not need to keep them for too long; a day or two should be good enough.

Another of my SQL Server backup best practices is to use backup compression if you can. Backup compression was a new feature in SQL Server 2008. Unfortunately, in that version it was only supported in the Enterprise Edition. In SQL Server 2008 R2 Microsoft decided to make this feature available in the Standard Edition. Backup compression has several benefits: Databases backup faster, they also restore faster and they save you a lot of storage space.

Checklist: SQL Server backup best practices

  • You can never have enough backups
  • Use differential backups
  • Backup compression is your friend
  • Test the restore process to ensure you have valid backups
  • Get your backups to a different geographical location

The only downside is that the SQL Server CPU use is higher while the backup is executing. But considering you can do backups off hours or at least during lower usage periods, this shouldn't represent an issue for the vast majority of database servers. After all, most database servers are more I/O-bound than CPU-bound. If you use the built-in Database Maintenance Plans feature, the backup task allows you to select backup compression. I recommend that you modify the server setting for database backup compression and change the default OFF to ON. This way a backup will be compressed even if the WITH COMPRESSION option is not included in the backup command.

Another important task is to test the restore process and make sure the backups are valid. The backup task in the Database Maintenance Plans has a checkbox where you can specify that SQL Server should check the integrity of the backup. Be sure to keep this on because if the backup is unusable, the maintenance plan will flag it as a failure and notify you right away. But do not think that you can always restore just because a file is considered OK by the restore verification feature. Obviously you cannot test restoring each daily backup, but at the very least you should set up a schedule and test restoring of each database once in a while, perhaps once a month.

And, finally, get a copy of the backups to an alternate location. If you back up to a local drive, make sure those files get backed up to a tape or to another network location. This protects you against the loss of the database server. But, what if the whole data center gets damaged by fire, hurricane, shut down by the FBI (it did happen to a data center), or any other disaster type? For total protection, you should have a strategy in place that periodically moves tape backups to another geographical location.

Visit on Facebook and Twitter.

Dig Deeper on SQL Server Backup and Recovery