Common Issues with SQL Server Backups

I absolutely love talking about SQL Server backup and restores. It is one of my favorite sessions to give at user groups and conferences all over the world. I’ve also written a couple of books on the topic.

Over the many years of working with Microsoft SQL Server, I tend to see the same mistakes or issues happening over and over again. I thought I’d make a list of the issues around backups and discuss them here. Some of these issues are more critical than others and the purpose of this blog is to help readers and organizations think about their backup process and the importance of having proper backups.

1. Incorrect recovery models in use.

Way more often than I’d like, I find databases that are in simple recovery model that are critical to the organization. These are databases that are super important to the organization where any amount of data loss could pose financial and/or reputational loss to the organization.

Databases using the simple recovery model are completely acceptable for certain databases. Those that do not need point-in-time recovery, such as archive databases or those with static data are prime candidates for the simple recovery model. For those that do need point-in-time recovery, full or bulk-logged recovery model should be used. To be honest, I rarely see bulk-logged in use.

2. Forgetting about encryption keys

Often times I come across organizations that are utilizing SQL Server Reporting Service, leveraging Transparent Data Encryption, or in some cases native backup encryption. In each of those scenarios, in order to recover the ReportServer database, restore a TDE encrypted backup, or restore backups taken with native backup encryption, you’ll need the encryption key. If you are having to restore to another server, you’ll have to restore/create the key from a backup.

Just as important as having access to the backup of the key, they were backed up and encrypted with a complex password. In order to create the key from backup, you have to decrypt the file using the complex password that was used when the key backup was taken. Proper key management is a huge requirement when you start utilizing encryption.

3. Offsite backups?

This issue is very self explanatory. Backups are only good if you can restore them. If you are backing up to the same server or same storage array, you have a very significant risk. If something happened to the server or storage array, you lose the server and backup files. Most systems I’ve reviewed in the past few years either have a solution in place to replicate the backups to another server or they are backing up to a network share. That is better, but still isn’t the best.

If something were to happen to your entire location, you will need those SQL Server backups somewhere other than your primary data center. Copying backups to Cloud storage is relatively inexpensive and provides a much higher level of protection. This should be a consideration for all systems, not just database backups. This should be part of any DR plan.

4. Bad choice of backup solution – OS snapshots vs SQL Server backups

Another issue that I see often is organizations relying on a third party backup solution that is only taking OS level snapshots of their SQL Server environments. The organization believes they are protected, however if they ever needed to restore, they would find out a little too late that their backup solution is inadequate and they lose crucial data.

For those tools that are backing up SQL Server on a regular basis, they are likely using the Volume Shadow Copy Service (VSS). Way too often I find that these tools, while they are taking backups have an unwanted side effect of the system freezing all database I/O. This can cause significant performance issues.

For this reason, I am a huge fan of using native SQL Server backups.

5. WITH CHECKSUM not being used

A quick and easy step to be adding to a SQL Server backup process is to include WITH CHECKSUM. This is not enabled by default. Enabling WITH CHECKSUM for your SQL Server backups, the system will verify the checksum values to make sure they match the checksum at the time the backup was written. This is a very valuable insurance policy to increase your confidence that your backups are good and restorable.

6. Backup Encryption

Encryption is a complicated topic. Transparent Data Encryption is becoming much more widely used on-premises and in the cloud. For Azure SQL Database and Managed Instance, TDE is on by default for all newly created databases. One of my favorite added benefits of TDE is that backups are encrypted.

For databases not taking advantage of TDE, SQL Server 2014 introduced native SQL Server backup encryption.

Why is backup encryption important? Databases are where sensitive data is stored. If you are placing unencrypted backup files on network shares or third party backup solutions that any administrator has access to, all they need is a copy of SQL Server developer edition and they can restore and view data. This is a huge risk.

7. Validating Backups by Testing Restores

I always tell people that the absolute best way to know that backups are good is to restore them. I encourage all organizations to regularly test backups by restoring them. This can even be built into an automated process.

Regularly restoring backups to test them looks good to auditors and examiners too.

Regularly restoring backups helps you know how long a restore would take. I’ve yet to find a production database server that did not have some type of service level agreement attached to it. If you have a 4 hour SLA for a server to be online and the restore of the full backup takes 6 hours, you can’t meet that SLA, so in that case, maybe an HA solution is needed to help mitigate the risk. It is also worth mentioning that the SLA begins when the issue arises, in some situations, we may be waiting on someone else to restore functionality before we can begin a restore, which cuts into our restore time. Example, network is down, storage is unavailable, the VM is being recovered, and so much more.

Knowing how long it takes to restore an environment is valuable knowledge.