Verifying Backups

  • Article

Before you restore a backup file, you must ensure that it is valid and that the file contains the expected backups. You can use SQL Server Enterprise Manager to view the property sheet for each backup device. For more detailed information about backups, you can run the following Transact-SQL statements:

  • RESTORE HEADERONLY. You use this statement to obtain header information of a particular backup file or backup set. If more than one backup resides on a backup file, SQL Server returns header information for all backups that are contained in that file.

When you run the statement RESTORE HEADERONLY, you receive the following information:

  • The backup file or backup set name and description
  • The type of backup media that was used
  • The backup method, such as full database, differential, transaction log, or file backup
  • The date and time that the backup was performed
  • The size of the backup
  • The sequence number of a particular backup within a chain of backup files
  • RESTORE FILELISTONLY. You use this statement to obtain information about the original database or transaction log files that are contained in a backup file. Running this statement can help you avoid restoring the wrong backup files.

When you run the statement RESTORE FILELISTONLY, SQL Server returns the following information:

  • The logical name of the database and transaction log files
  • The physical names of the database and transaction log files
  • The type of file, such as a database or transaction log file
  • The filegroup membership
  • The backup set size, in megabytes (MB)
  • The maximum allowed file size in MB
  • RESTORE LABELONLY. You use this statement to obtain information about the backup media that holds a backup file.
  • RESTORE VERIFYONLY. You use this statement to verify that the individual files that make up the backup set are complete and that all backups are readable. SQL Server does not verify the structure of the data that is contained in the backup.

For information about running Transact-SQL statements, see SQL Server Books Online.

Copyright © 2005 Microsoft Corporation.
All rights reserved.