Restore Verify Only option vs Manual Restore

Question

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?

Answer 1

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

Answer 2

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. )