Validation of Backup

Database backup are very important for organizations specifically a database, we need to ensure that backups created are valid and useful. To validate the backup, we can use following command

The command will check the backup file and return a message of whether the file is valid or not. If it is not valid, this means the file is not going to be useable for a restore and a new backup should be taken. One thing to note is that if there are multiple backups in a file, this only checks the first file.

Restore verifyonly from <backup device>

RESTORE VERIFYONLY FROM DISK = ‘D:\MSSQL_BACKUP\My_DB_backup.bak’

When a backups is validated, SQL Server performs the following steps:

Calculated a checksum for the backup and compare to checksum stored in the backup files
Verify that the header of backup is correctly written and valid
Transits the page chain to ensure that all pages are contained in the database and can be located

Check a backup file on disk for a particular backup

This command will check the second backup in this backup file. To check the contents in a backup you can use RESTORE HEADERONLY and RESTORE FILELISTONLY; use the Position column to specify the FILE number.

Restore verifyonly from <backup device> with < file>

RESTORE VERIFYONLY FROM DISK = ‘D:\MSSQL_BACKUP\My_DB_backup.bak’ With File = 2