My question is related to best practices with SQL Server. I need to put together a document that lists our various databases and that the backups we have of these databases are valid and have been tested and we know that they will restore properly in case of a disaster recovery scenario. I'm using the Ola H. maintenance solution which backs up the databases with the RESTORE VERIFY option and gives a "succeeded" message to indicate that the backupset is valid. Is this enough to be confident or should we still be manually restoring these backups to a test server and running tests?

  • 1
    Schrodinger's backup: The condition of any backup is unknown until a restore is attempted (originally penned on twitter by @nixCraft). RESTORE VERIFY doesn't restore any data, it only verifies the backup header. @Kin's answer in the duplicate question does a great job of running through it all. – John Eisbrener Nov 9 '17 at 15:02

Is this enough to be confident or should we still be manually restoring these backups to a test server and running tests?

To be 100% confident, a restore of database is required. You need a seperate server to do the restores. Since you are using Ols's backup solution, the entire restore and verify process can be automated using dbatools - Restore-DbaDatabase esp specifying parameter MaintenanceSolutionBackup

e.g. Below powershell script Scans all the backup files in \\server2\backups$ stored in an Ola Hallengreen style folder structure, filters them and restores the database to the c:\restores folder on server1\instance1

Restore-DbaDatabase -SqlServer server1\instance1 -Path \server2\backups\$ -MaintenanceSolutionBackup -DestinationDataDirectory c:\restores\

Also refer to my answer : Understanding the impact/risk of turning off “verify backup integrity” on SQL backup

|improve this answer|||||
  • Why do you need a separate server? Just restore the databases as a different name, and put the files in a different drive or folder on the same server. – James Jenkins Nov 9 '17 at 15:56
  • @JamesJenkins Not absolutely required to be a separate box, but most companies don't want to add the overhead of that IO and CPU work to their existing production servers. – BradC Nov 9 '17 at 15:57
  • We just began using the DBATOOLS PowerShell kit. This answer is the most applicable to my environment. I will proceed with this. The other answer "Understanding the impact/risk of turning off verify backup integrity on SQL backup" was also very helpful. – DtechNet Nov 9 '17 at 18:13

More than simply "insufficient", I would actually argue that running RESTORE VERYIFYONLY after every backup is, on modern hardware, practically useless and (therefore) a waste of time and IO. See the extended discussion here.

Literally all RESTORE VERIFYONLY does is make sure the destination backup file is physically readable, and that the header looks like a properly formed backup header. This isn't completely useless, but the fact that it reads the entire backup file off disk means it typically takes just as long as the original backup step.

Now this was arguably important when we were saving SQL backups directly to physical tape, but that is increasingly rare.

RESTORE VERIFYONLY doesn't validate that the backup in any way matches the content of the original database, nor does it make any attempt to validate that the remainder of the backup looks right, unless you add the WITH CHECKSUM parameter:

(24p) using RESTORE … WITH VERIFYONLY validates the entire backup

No. Using VERIFYONLY only validates the backup header looks like a backup header. It’s only when you take the backup using WITH CHECKSUM and do RESTORE … WITH VERIFYONLY and using WITH CHECKSUM that the restore does more extensive checks, including the checksum over the entire backup. )

|improve this answer|||||

Not the answer you're looking for? Browse other questions tagged or ask your own question.