Test SQL Server backups to avoid Schrödinger’s backups

299

This post is my guideline on how to handle and test SQL Server backups to ensure their viability and avoid a “Schrödinger’s backup” scenario.

What’s Schrödinger’s backup?

The term is derived from Schrödinger’s famous thought experiment where a cat inside a box is both alive and dead (a quantum superposition of these two states) until an observer uhm… well… observes the state of the cat.

Similarly, a SQL Server database backup is both viable and non-viable until it’s actually restored and DBCC CHECKDB doesn’t return any errors.

The usual scenario

Folks think that if they just take backups regularly, and their backup intervals match their recovery point objectives, then they can rest assured knowing (more like falsely thinking) they have backups from which to restore in case something bad happens to a database or to the entire instance.
Then, something unexpected happens like:

• Someone forgets a WHERE clause in an UPDATE or a DELETE.
• Someone accidentally drops a table/the entire database.
• Database corruption rears its ugly head.
• Or worse, the entire server gets encrypted by ransomware.

That list isn’t exhaustive, but none of those points are fun and they almost* always require one or more databases to be restored.

And this is where Schrödinger might have something to say, if your backup is also corrupted, and you haven’t previously tested it then this is when you’ll find out.
Yup, at the worst possible time.

*If you get data corruption in a nonclustered index, but not in the table itself, you can get away with simply rebuilding the index.

How can a SQL Server backup end up being corrupted?

This depends on a lot of variables, but a few examples would be:

• Backing up an already corrupted database.
• Doing backups with CONTINUE_AFTER_ERROR which might cover up issues that would cause backups to fail otherwise.
• Corruption/bit rot on the storage where the backup files live.
• Someone messing around with the backup file in Notepad++. (I wish this was a made-up scenario)

Avoiding Schrödinger’s backups

Similar to security, DR readiness is a layered approach. So, I’m going to split this in the four main stages of a backup’s lifecycle.

1. Before the backup is taken

1.1. Configure alerts for database corruption

First, as an initial configuration, alerts for database corruption should be set up.
Database corruption related errors are: 823, 824, and 825.
This is a one time step and should generally be part of the SQL Server post-install config process.

If you haven’t configured alerting for them already, Brent Ozar has a post with the T-SQL required to set those up along with other important errors.

In case you’re not sure if alerts for database corruption are set up in your environment, both sp_Blitz‘s basic execution

As well as PSBlitz‘s “Instance Health” page, regardless of check type, will point that out.

1.2. Check your databases’ integrity with DBCC CHECKDB

At every place I’ve had to design the backup and DR processes, I’ve always added a step to do an integrity check on the databases shortly before the full backup was taken.

The reason behind it is twofold:

1. Ideally, you’d want to know about data corruption before you take a full backup.
2. Depending on your backup retention policy, the current full backup might cause an older, but consistent, full backup to be deleted.
   And that’s the opposite of what you’d want to do in case of corruption.

Here, you have the option to either roll your own process revolving around DBCC CHECKDB, or use Ola Hallengren’s DatabaseIntegrityCheck.
Personally, I’ll always opt for the latter, but some orgs might not feel comfortable with 3rd party stored procedures.

If you’re not sure when your last database integrity check was done (if ever), you can use sp_Blitz

Or an instance-wide in-depth* check with PSBlitz will also point out if the latest DBCC CHECKDB was more than 2 weeks ago.

*Although I’m considering making it part of the default/basic check as well.

If your databases are on the larger side and/or if your instance is almost always in use leaving you with shorter maintenance windows where DBCC CHECKDB could be run, you have three options:

1. Use DBCC CHECKDB with the NOINDEX option to skip nonclustered indexes, and/or limit the depth of the check with PHYSICAL_ONLY.
2. If you’re using Always On availability groups then you can run DBCC CHECKDB on the secondary replica.
   Obviously, it’s not the same thing, and storage corruption on the primary host could not be detected this way, but it’s better than nothing.
3. Only rely on the DBCC CHECKDB done as part of the test restores.
   This makes the test restores even more important.
   

1.3. Check your alerts

After alerting and integrity checks are put in place, never sleep on corruption related alerts.
The sooner you act on them, the better.

I know this seems obvious, but, in some cases, corruption alerts may just end up being lumped in with other low priority stuff.

2. When taking backups

Just a heads-up here that you can use Ola Hallengren’s dbo.DatabaseBackup stored procedure for backups.

Like the rest of his maintenance solution, it’s very robust and flexible.
It also works with 3rd party backup tools like Redgate SQL Backup, Idera SQL Safe Backup, and Quest LiteSpeed.

Here’s an example of how you can execute it to do full backups of all the user databases on an instance, with compression, checksum, encryption using a server certificate*, and backup verification.

*If you’re encrypting your backups, make sure you also back up the certificate and its private key to a secure location. An encrypted backup without the ability to decrypt it is just another form of Schrödinger’s backup.

2.1. Add the CHECKSUM option

Ideally, your backup command, besides the usual compression and encryption options, should also include the CHECKSUM option.
This will make the backup process verify each page for checksum (and torn page if you’re into retro options), and generate a checksum for the entire backup.

Just note that there might be a performance overhead added by the CHECKSUM option.

2.2. Do NOT use CONTINUE_AFTER_ERROR

CONTINUE_AFTER_ERROR, as mentioned earlier, ensures the backup process completes even if it ran into a page checksum error.
Practically defeats the purpose of the CHECKSUM option, since you’re telling SQL Server to keep going even if checksum errors are encountered.

2.3. Add a step to do RESTORE VERIFYONLY

This step should take place right after the backup finishes.
VERIFYONLY tells the restore process to just verify the backup without actually restoring the database.

And the output is pretty self-explanatory:

The backup set on file 1 is valid.

It verifies for database backup set completeness and if the backup file can be read properly, but it does not remove the need to test your SQL Server backups.

2.4. Don’t be too quick to delete previous backups

If you only keep one backup cycle’s worth of backups (i.e.: the current full backup will cause your oldest full backup to be deleted), I’d argue for extending that to at least two backup cycles.

Or at least deleting the old full backup only after this one has passed through the test restore + DBCC CHECKDB process without any issues.

3. Actually test restore your SQL Server backups

While all previous steps were about reducing the chances of getting a corrupt backup to begin with, this is what actually guarantees that the backup you’ve taken is viable and can be restored from when needed.

I generally recommend using a separate VM and SQL Server instance specifically for doing test restores.

And, if you’re concerned about licensing costs, you can use SQL Server Developer Edition for this.
Since this specific scenario is covered in the licensing agreement.

You cannot use Developer Edition to build test data and move that same data into production.
But you can restore a production set of data backup for testing purposes.

3.1. Test restore and integrity check your latest backups

The goal here is to ensure that the newly created backup can be restored from and also does not have any data corruption.

This step can be easily automated with dbatools’ Test-DbaLastBackup which also runs DBCC CHECKDB by default on the restored databases.

Or, for more flexibility, you can roll your own process using:

• dbatools’ Restore-DbaDatabase to handle the restore part.
  Restore-DbaDatabase -SqlInstance TESTRESTOREHOST\SQLTEST01 -Path "\\PRODHOST01\Backups"
  If you’re using Ola’s dbo.DatabaseBackup stored procedure, add -MaintenanceSolutionBackup to the Restore-DbaDatabase command so that it recognizes the specific directory structure.
• Ola Hallengren’s dbo.DatabaseIntegrityCheck stored procedure to handle the actual integrity check on the test restore instance. Because Restore-DbaDatabase doesn’t include a post-restore integrity check.

In a perfect world, this should take place with the same cadence as the differential backups.
So the test restore will use the current full backup and the most recent differential one.

Realistically, in the case of large databases, weekly would be more feasible.
In this case the most recent t-log backups since the last diff can also be added to test the full log chain for point-in-time recovery.

Note that Test-DbaLastBackup drops the restored databases when the test is done.
If you’re not using Test-DbaLastBackup, then remember to drop the restored databases and clean up the copied backup files to keep the test instance tidy for the next cycle.

3.2. Test restore and integrity check your long term backups

This is fairly similar to the previous point, except that:

• It’s meant to ensure that the backups are still viable after x days/months/years on your long term storage.
• You can’t use Test-DbaLastBackup for this one since it relies on backup information that’s available on the source instance. But you can use the second method with Restore-DbaDatabase and dbo.DatabaseIntegrityCheck.

Generally, I’ve done this on a quarterly basis.

3.3. Have alerting put in place for failed test restores

Make sure your automated test restore process sends alerts on failures.

Aside from alerting, both successful and failed test restores should be logged.
This data can come in handy during postmortems and/or for audit purposes.

3.4. What if a test restore fails?

This depends on where exactly it fails.
If DBCC CHECKDB finds corruption on a nonclustered index, no biggie just rebuild it, do the same in the source database, and move on.

But if the backup cannot be restored or DBCC CHECKDB finds corruption in one or more tables then things get a bit more complicated and would involve the following steps:

1. Investigate the source of corruption.
   Is it just the backup? Is it in the source database as well?
2. Check if an older backup is viable.
   If it’s also in the source database, then we’ll need to make sure that viable backups still exist.
3. Assess data loss exposure.
   How old are the most recent viable backups and how comfortable are we with restoring from that date?
4. Communicate with stakeholders.
5. Get in touch with Microsoft Support or a consultant with corruption repair expertise.

4. When storing and eventually deleting backups

4.1. Have multiple copies of your backups

My preferred backup copies layout is:

• Store a copy of the current backup cycle on the same host as the instance.
  My go-to is the latest full, the 2 most recent diffs, the t-log backups that go with those 2 differential backups.
• Store copies of both the current backup cycle together with the long term backups on a network share or in a dedicated tool like Veritas NetBackup.
• Ideally store a 3rd copy of your long term backups either in the cloud (e.g. Azure Blob Storage which also offers immutable storage for additional protection) or in a separate location/data center.

I’m a fan of this layout because it covers the 3-2-1 backup rule.
3 backup copies: 2 on different mediums, 1 off-site.

And the backups stored on the same host as the instance allow for quick restores without having to go through other steps to make those backup files available.

4.2. Backup retention and deletion policies

Ideally, old backups should only be deleted when the new backup is guaranteed to be viable (i.e. it went through a successful test restore).

This applies especially to the previous full backup stored on the instance’s host.
Because if something happens that’s the one you’ll be reaching for first.

Other things to keep in mind

System databases

The master and msdb databases are also vital for an instance, and, while you can wing it and potentially recover master from a certain type of corruption, it’s safer to have viable backups of both.

My rule of thumb is to take a full backup of master and msdb every time a differential backup runs on the instance.

And for test restoring them the only requirement is to restore them with different database and file names.

T-SQL example:

dbatools example using Test-DbaLastBackup to restore master on the same (source) instance and run an integrity check:

Out-of-band backups

Full backups that aren’t part of your normal backup schedule (e.g.: when the devs want a fresh copy of the production database on their development instance) should always be done with the COPY_ONLY option.

T-SQL example:

SSMS UI example:

dbatools example:

This ensures that the one-off backup won’t mess with your regular backup process since it doesn’t reset the LSN*.

*LSN stands for Log Sequence Number and is SQL Server’s way of identifying which subsequent diff and t-log backups go together with a given full backup.

Initial steps when corruption is detected on a database

Regardless if it’s during a scheduled integrity check or from an alert, the first two things I’d recommend doing would be:

1. Identifying the corrupt object.
   If it’s a nonclustered index, it’s not a big deal and can be fixed with a simple index rebuild.
2. If it’s the actual table then I’d pause any backup deletion job/policy for that database or instance while I investigate the issue further.
   I’d also make sure that the most recent full backup has been successfully tested and is viable in case I need to restore the database.

Conclusion

Don’t let that quantum superposition get you and test your SQL Server backups as often as possible.

Unfortunately, there is no single silver bullet for backup viability.
It requires a layered approach with alerts to catch corruption early, integrity checks before the backup runs, checksums and verification during the backup process, and regular test restores to prove the whole chain works.

Not to mention that it’s a hassle to plan and initially set up, but it’s way better than the alternative and I hope my (lengthier than intended) post reduces the amount of effort and guess work that you might have to put into this.

Feel free to add a comment if you spot any gaps or have any additional recommendations as part of your process to test SQL Server backups.

Brand and product names disclaimer

This isn’t a paid post.
The commercial products I’m linking to are just examples for the given scenarios.
Out of all the products I’ve mentioned, I only have experience with Azure Blob Storage, Veritas NetBackup and Idera SQL Safe.