SQL Server Exercise: Verifying Backups

Description
Requirements
Setup
GUI Steps
T-SQL Steps

Description: (back to top)

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.

Requirements: (back to top)

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

Setup: (back to top)

None.

GUI Steps: (back to top)

1) Right click on the Northwind Database in Enterprise Manager and choose "All Tasks", "Backup Database"

Enterprise Manager screen shot - right click on Northwind Database, choose All Tasks, Select 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.

Backup Database screen - Provide database name, backup name, backup type (complete), backup destination, and click on the options tab

3) Check the "Verify backup upon completion" option and click the "OK" button.

Screen shot of backup database options tab - click on Verify backup upon completion 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.

Screen shot notification that backup and verification processes both worked great - click the OK button

T-SQL Steps: (back to top)

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.

Screen shot in query analyzer of using RESTORE HEADERONLY command to find the file number needed for the RESTORE VERIFYONLY command used in the next step

3) We now run the following command using RESTORE VERIFYONLY which assures us that are backup set is good.

Screen shot of using RESTORE VERIFYONLY to confirm that our backup is sold in Query Analyzer

SQL Server Exercise: Verifying Backups

Description: (back to top)

Requirements: (back to top)

Setup: (back to top)

GUI Steps: (back to top)

T-SQL Steps: (back to top)

Oracle Backup Info

Oracle Exercises

SQL Server Backup Info

SQL Server Exercises