Validation of Backup

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

more

Advertisements