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?
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
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
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
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.
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:
RESTORE … WITH VERIFYONLYvalidates the entire backup
VERIFYONLYonly validates the backup header looks like a backup header. It’s only when you take the backup using
WITH CHECKSUMand do
RESTORE … WITH VERIFYONLYand using
WITH CHECKSUMthat the restore does more extensive checks, including the checksum over the entire backup. )