Verify Backups With Test-DbaLastBackup

Is the process of testing your backups something you know you should do but never get around to? Do you find yourself assuming all is well with backups while putting out other fires? Test-DbaLastBackup, part of the beloved dbatools, can solve your dilemma.

There are many options available when using Test-DbaLastBackup. Let’s explore a few of these options and see some examples of how to use them.

No Excuses

Whether you have a single database on a server or hundreds of databases across many servers, Test-DbaLastBackup helps manage the heavy lifting that comes with verifying your full backups. Once given a few commands, it can restore a copy of the database, run DBCC CHECKDB to check for issues, and drop the database copy when it’s complete.

Starting with the basics, we can point to an instance and database to test:

Test-DbaLastBackup -SqlInstance CALLIHAN\MSSQLSERVER02 -Database ScriptHQ

We see from RestoreResult that our database was successfully restored. DbccResult indicates our checks were completed successfully as well.

It may also be worth tracking how long the processes are taking, so it’s nice to see those listed. I didn’t have many for this example, but you may also find a longer list in BackupFiles if you have a bunch of transaction log backups involved.

What if we only want to verify the backup and skip the restore? We can run the same script as above but add the -VerifyOnly option:

Test-DbaLastBackup -SqlInstance CALLIHAN\MSSQLSERVER02 -Database ScriptHQ -VerifyOnly

One last example: if we want to test the restore process but skip out on the DBCC checks, we can add -NoCheck. Perhaps we’d rather leave the test restore database to perform our own checks later. In that case, we can also include -NoDrop:

Test-DbaLastBackup -SqlInstance CALLIHAN\MSSQLSERVER02 -Database ScriptHQ -NoCheck -NoDrop

If you do this, make sure to drop your test restore database before you run Test-DbaLastBackup again. If Test-DbaLastBackup runs but finds the test restore database, it will give you a warning and skip doing any restores or checks.

Necessary

Testing your backups may seem like busy work to some. Let’s face it, it’s not as exciting as tuning a query to cut execution time in half or saving the day by getting a database back online. But that makes it even more worthwhile to add Test-DbaLastBackup to your arsenal of tools. Take some pain out of backup testing and give yourself some faith that your backups are safe.

Thanks for reading!