Improve your planning for SQL Server backups

by May 28, 2022

Not understanding recovery needs

The whole point of creating backups is to retrieve data in case data is lost or there is a failure. But to set up the proper backup and recovery plan, you need to understand what needs to be recovered to determine if the recovery was successful or not.

You need to make sure you understand your recovery needs for each database and application that you support. Otherwise, your recovery may fall short.

Not defining roles

In many environments, the database administrator is responsible for the databases. But the network group, storage group, or backup group is responsible for backups. While this is fine, the question remains: Who recovers the databases? If there are multiple groups involved, who takes the lead for recovery?

So, make sure that along with knowing who is doing the backups, you also know who is responsible for restores and all the steps required for a successful recovery. In addition, you must understand who will perform each step. As a result, you then know that the recovery will occur as quickly as possible rather than you scrambling to find out who will do what.

Wrong backup plan and/or recovery model

Often you will find someone who is not expecting to lose any data. But their databases are in the Simple recovery model and they are only doing full backups. Another scenario is where the database is in the full recovery model and only full backups are being issued.

You should take the time to understand the differences between recovery models and the different backup options as these two items go together.

I only need to recover the databases

You need to know that there is more to recovering SQL Server than just the databases.

When setting up your database backup plan, you should be sure to remember to put into place backups for Reporting Services, Analysis Services, Integration Services, as well as items that may be outside SQL Server. This includes third-party tools, sqlcmd files, and batch scripts. Often people are only thinking about the databases, so make sure you encompass the entire SQL Server environment.

Read the whitepaper “Top SQL Server backup mistakes (and how to avoid them) by Greg Robidoux from MSSQLTips to learn more about improving SQL Server backups..

[Download PDF]