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