Why you should keep track of your backup

For many, backing up your data is a given, but the question is whether you also keep track of the backup. It can lead to very unpleasant stories if the information that was backed up cannot be restored. What good is a backup if it's not usable? Not much! That's why you should keep an eye on your backup.

Full control over your backup.

In these times, it is important to keep an eye on your backup, as the risk of being exposed to cyber attacks or other threats has increased significantly. In a vulnerable environment, anything can happen, and it is therefore important to ensure that you have full control over your backup. By having control, you minimize the risk of the damages becoming too great to be repaired if an attack were to occur.

We receive a lot of inquiries about corruption in databases. When investigating the options for repairing the damage, it has been found that the backup has not been as well-managed as previously thought. It can be a very costly affair for the company if data is lost in business-critical systems.

Below are a few examples from real life as well as some things to consider when configuring your backup solution.

Example 1: About having to start over

A customer contacts us and reports a problem with a database. The log file is missing. We try to start the database without a log file, but it ends up in Recovery Pending because it cannot replay what should be in the log file. We try to retrieve the database in emergency mode. Then we can access the contents of the tables with SELECT, but we do not know what is damaged or missing. Running DBCC Checkup with the REPAIR command did not work either. As a result, we have to start over.

The only alternative for saving the database is to restore it from a backup. Unfortunately, no backup was created for one of the databases. The system was still in the configuration phase. The decision had to be made to start over with the configuration and customization, resulting in the loss of two weeks of consultant work and a delay in the schedule.

Conclusion: Keep track of your backup, it can save both time and money.

Example 2: Issues with SAN and much bigger challenge than expected.

A customer contacts us on a Monday because they had issues with their SAN (Storage Area Network) over the weekend, and backup with transaction logs failed due to corruption in the log files. We solve the problem by first adjusting the databases to SIMPLE RECOVERY MODEL and then to FULL RECOVERY MODEL. After that, we take a full backup, and log backup starts again.

As an additional check, we also run a DBCC CheckDB on all databases. It turns out that four databases also have corruption in the data file. Our first choice is to restore from the latest working backup. Unfortunately, it has already been removed from the disk, so we turn to the third-party application that was supposed to archive the files for long-term storage. Now it turns out that the application has crashed, and we can only get a backup that is three weeks old.

We managed to save three of the databases, but the fourth database could not be saved. Restoring from the old backup meant that three weeks of data were now missing.

Conclusion: Check that your backup actually works.

Example 3: Database Analysis - Health Check.

During a review at a customer's site, we discovered that the backup schedule looks as follows:

• Full backup ONCE a day at 11:00 PM
• Log backup ONCE an hour
• File backup ONCE a day at 1:00 AM

The SQL backup job removes all old backups that are older than 15 hours. This means that log backups between 1:00 AM and 11:00 AM will not be included in the long-term storage. If a restore needs to be done to a specific point in time, it will be impossible because there are missing log backup files to restore from (the backup chain is broken).

Conclusion: You need to keep track of the entire backup chain.

To truly have a backup/restore process that works, there are many things to consider. Here are some things that we believe are important to keep track of:

• Perform backups of the databases that meet your organization's RPO (acceptable data loss) requirements.
• Ensure that your file backup works correctly and takes into account time so that you get all files.
• Regular (preferably automated) restore tests with DBCC CheckDB included.
• Regular integrity checks with DBCC CheckDB.

  
Think carefully about your requirements and desires regarding RPO and RTO before setting up your backup/restore process. It can save your job!