Verifying Ensures Files Have Been Written to Backup Set and are Readable
Description:
Verifying backups is almost always a good idea with SQL Server. Verifying the backups that you have made ensures that all files have been written to the backup set and that the files are all readable. You may experience a performance hit to the server, and the backup process will probably take a bit longer, but knowing that you have a good backup may be worth it to your organization.
(back to top)Requirements:
- As with all of the exercises on this site, do not perform this exercise on a production server. Perform this exercise in a closed development environment
- You will need to have a backup device already created - for the exercise on creating a backup device, click here (back to top)
Setup:
None.
(back to top)GUI Steps:
1) Right click on the Northwind Database in Enterprise Manager and choose "All Tasks", "Backup Database"
2) Choose the Northwind database and name the backup. Choose "Database - complete" as the backup option. Choose a destination backup device, and then click on the "Options" tab.
3) Check the "Verify backup upon completion" option and click the "OK" button.
4) You will receive notifications that the backup has completed successfully followed by a message that the backup and verification both completed successfully.
T-SQL Steps:
1) In Query Analyzer, backup the database using the BACKUP DATABASE command.
2) We need to determine which backup we need to restore. Run the RESTORE HEADERONLY statement on the verifytest device. We only have one backup on the verifytest device, so file number 1 is the file number that we are going to check to make sure it is a good backup.
3) We now run the following command using RESTORE VERIFYONLY which assures us that are backup set is good.
