We encourage you to take your database backup strategy very seriously because it is crucial for keeping your data safe and sound. In this article, we are going to explore things that will make sure that you choose the best backup strategy for your SQL Server database. Everyone knows that before considering database backup strategy it is necessary to consider Recovery Point Objective (RPO) and Recovery Time Objective (RTO). But this is not an exhaustive list, here we have prepared some more questions, answers to which will enable you to create a backup strategy that will meet all your needs and expectations.

How Much Data Can You Afford to Lose in Case of Failure?

In other words, it is a Recovery Point Objective (RPO). When we are talking about RPO you need to keep in mind the following information:

• Have you tested your backups and are you sure that you can restore them at least to a particular point in time?
• Should a failure occur and you lose your entire database, how much data can you afford to lose after you restore the database?

How Much Time Can You Spend on Restoring Your Database?

It is also known as a Recovery Time Objective (RTO). You need to understand how long it will take to restore the entire database from backups. So, here is what an RTO means:

• Where database backup files are stored and how fast can you retrieve them?
• How much time do you need to bring up and configure a server if you don’t have one?
• How much time does it take to restore your database?

How Much Data Will You Lose if at Least One Backup is Corrupted?

Backups accumulate with time and therefore require more storage space. It is a well-known fact that people don’t use their database backups often and usually keep them in a single place. It means that if this database backup file will be accidentally corrupted or even deleted, you will lose this information for good.

Let’s assume that your backup chain is a full backup once a day and transaction log backups every hour. In this case, your backup chain ends at the last transaction log. Even if you lose the last full backup you will still be able to restore your database with transaction log backups.

Here’s another scenario: you have a full backup once a week and differential backup every night, here all differential backups depend on their full backups, and such a database backup strategy isn’t safe. If you lose a full backup you will be able to restore your database with the help of differential backups that belong to the full backup you have.

We highly recommend keeping your database backups in more than one place, for example, you can store backups on your computer and somewhere in the cloud.

How Much does Backup Execution Impact Performance?

When you start the database backup process it will take some resources such as Disk I/O (Input/Output), RAM, CPU, Network. It’s OK if your database is not big, but what if you administer a huge database? In such a case, you need to perform a backup as quickly as possible.

When performance is critical you should pay attention to the following items:

• Make any backup type with the appropriate frequency. Avoid accumulation of large amounts of information as it might lead to extended backup times and impact overall performance.
• Use backup compression in spite of the fact that such an operation burns more CPU it takes less time to make a backup.
• Take care of the perfect throughput to the storage device to increase the recording speed of backups.

How do You Know That All Your Backups are Successful?

Imagine that you administer at least ten databases and, I believe you would agree with me, it is really hard to keep everything in your mind. What if a database backup job failed and you didn’t notice it? Another notorious case is when all backups are being made as scheduled and everything seems to be perfect, but then, when you need to restore the backup file you notice that this file is corrupted.

• Here we recommend you, if you use a third-party tool for backups, to set up email notifications to alert you in case of a backup failure.
• Regularly check your backups. Sharpen your database restoration skills on another server or environment.

We hope you find the above tips useful and helpful in your way of creating the best database backup strategy that will keep all your data safe at all times.