Making Sure Backup is Set Ready for Recovery

After executing the BACKUP statement, the selected device (tape or disk) contains all data of the object you chose to back up. The stored data is called a backup set. Before you start a recovery process, you should be sure that

• The backup set contains the data you want to restore
• The backup set is usable

The Database Engine supports a set of Transact-SQL statements that allows you to confirm that the backup set is usable and contains the proper data. The following four statements, among others, belong to it:

• RESTORE LABELONLY
• RESTORE HEADERONLY
• RESTORE FILELISTONLY
• RESTORE VERIFYONLY

The following subsection describes these statements.

RESTORE LABELONLY

This statement is used to display the header information of the media (disk or tape) used for a backup process. The output of the RESTORE LABELONLY statement is a single row that contains the summary of the header information (name of the media, description of the backup process, and date of a backup process).

Note – RESTORE LABELONLY reads just the header file, so use this statement if you want to get a quick look at what your backup set contains.

RESTORE HEADERONLY

Whereas the RESTORE LABELONLY statement gives you concise information about the header file of your backup device, the RESTORE HEADERONLY statement gives you information about backups that are stored on a backup device. This statement displays a one-line summary for each backup on a backup device. In contrast to RESTORE LABELONLY, using RESTORE HEADERONLY can be time consuming if the device contains several backups.

The output of RESTORE HEADERONLY contains a Compressed column, which tells you whether the backup file is compressed (value 1) or not.

RESTORE FILELISTONLY

The RESTORE FILELISTONLY statement returns a result set with a list of the database and log files contained in the backup set. You can display information about only one backup set at a time. For this reason, if the specified backup device contains several backups, you have to specify the position of the backup set to be processed.

You should use RESTORE FILELISTONLY if you don’t know exactly either which backup sets exist or where the files of a particular backup set are stored. In both cases, you can check all or part of the devices to make a global picture of existing backups.

RESTORE VERIFYONLY

After you have found your backup, you can do the next step: verify the backup without using it for the restore process. You can do the verification with the RESTORE VERIFYONLY statement, which checks the existence of all backup devices (tapes or files) and whether the existing information can be read.

In contrast to the previous three statements, RESTORE VERIFYONLY supports two specific options:

• LOADHISTORY – Causes the backup information to be added to the backup history tables
• STATS – Displays a message each time another percentage of the reading process completes, and is used to gauge progress (the default value is 10)