Joey on SQL Server

The Right Approach to Database Backups

Elaborate backups might seem like a good idea at first, but they add unnecessary complexity to your recovery process. Here are some things to keep in mind to make backup and recovery as smooth as possible.

• By Joey D'Antoni
• 05/19/2021

As a database administrator (DBA), your No. 1 job role is to ensure that you do not lose data in the event of a system failure, user error or malware attack.

While many databases continue to move to cloud platforms like Azure SQL Database and Amazon RDS, where backups are handled by the cloud service (you want to understand that process, though, as it varies by service and the costs can get quite high for long-term retention), the core responsibility of database backups falls on the DBA or system administrator.

As a consultant and a DBA with over 20 years of experience, I've seen a wide variety of backup solutions. Recently, I've run into a number of clients where a (in every case, former) DBA tried to build a really elaborate backup solution that I'm sure they thought was amazing at the time it was built. Complexity can come in a lot of forms, but it's a good practice to keep your backup solution is simple as possible.

What Is the Best Approach?
If I am implementing a backup solution for SQL Server (or really any database) in a greenfield environment, my basic approach is to use native database backups backing up to a file share of some kind. If the server is running in Azure or has low network latency, I will backup to Azure storage.

I use Microsoft MVP Ola Hallengren's Maintenance Solution, which is an open source set of T-SQL scripts and SQL Server Agent jobs, to execute those backups and other maintenance activities. Hallengren is a Microsoft Data Platform MVP from Sweden. His code has been around for years and works for most cases. SQL Server includes built-in maintenance plans that can also execute backups and other system maintenance tasks, but I prefer Hallengren's code, as it is more transparent in the way it logs executions. Hallengren's solution also interacts with the backup tools from a number of third-party SQL Server backup vendors.

There are a myriad of options for SQL Server backup that you can adjust to meet your needs. However, there are a couple that I nearly always set. The first is striping the backup across multiple files. This can significantly improve your backup performance by effectively parallelizing the backup process. I typically recommend using a lower value of the number of CPU cores on your server, or eight files. If you have a particularly large server and large databases, you may see benefits beyond eight files, but the benefits are limited. Using multiple files is extra important if you are backing up to Azure, as a single file in blob storage is limited to 50MB/s, which dramatically limits your throughput.

The other setting that I always enable is compression, which improves both backup and restore times. You will still need the entire space on disk for an uncompressed backup, but once the backup is complete, you will typically see about a 30 percent reduction in file size.

Where Should Those Backups Live?
I mentioned two targets above: a file share and Azure Blob Storage. However, let's dig into underlying storage a bit more.

Network bandwidth and storage throughput are going to control the performance of your backup and restore process. Whatever you do, ensure that you are not backing to the same server that your database lives on, or even the same storage device. Both scenarios present a single point of failure, and while they may be acceptable short-term backup locations, you should move those backups to a separate storage location as soon as possible. Ideally, you would replicate your backups to a second physical location to protect against datacenter failure or ransomware attacks.

When I started working in IT and through much of my career, this involved shipping backup tapes offsite to a vendor like Iron Mountain. Now, with cloud computing, you have several options. Even if your services are on-premises, the cloud can be an attractive option as a cheap disaster recovery site. If you are cloud-native, I consider it a best practice to store backup files on a geo-replicated storage account for added protection.

Your infrastructure team may have a backup appliance with a specialty storage configuration and deduplication, which reduce space. While these can save a lot of storage, they can severely hamper restore performance. If your team has one of these appliances, perform a lot of restore testing with production data volumes to understand any potential performance overheard.

What About Infrastructure-Based Backup Solutions?
Backup software and services are a big space in the software industry. Both storage vendors and backup software vendors have "all-in-one" backup solutions that will back up your virtual machines (VMs), storage and databases.

These solutions are not created equally. Some may only take snapshots of your VMs (not your databases), and others may break your transaction log chain without you being aware of it. Some solutions may even cause failures in distributed SQL Server solutions like availability groups.

Not all backup solutions are like this; there are many that fully integrate with SQL Server's backups. A good question to ask of any backup vendor is if it can do a piecemeal restore or even a page-level restore of your database. While these restoration options are not common, they show that your backup vendor is fully integrated with SQL Server and can take advantage of all the available backup and restore options.

The goal of keeping your backups as simple as possible is to make your restore process as easy as possible. When your backup process is good, it makes it much simpler to test restores regularly, which should always be an important part of your backup strategy. Beyond making restore testing easier, in the event of an actual failure, having a repeatable and easy-to-execute restore process makes recovery much easier.