How to verify backup status?

Sometimes if backup job fails with weird error or at time restore command throws out an alien error first thing to do is to verify weather there is valid backup or not. Mainly in situations like server crash or un-expected DB corruption we first should check weather we have a valid backup of database or not. We several options associated with RESTORE command to verify the backup status/validity.

Backup Verification Options:

----------------------------

•RESTORE HEADERONLY     - gives you a list of all the backups in a file

•RESTORE LABELONLY        - gives you the backup media information

•RESTORE FILELISTONLY    - gives you a list of all of the files that were backed up for a give backup

•RESTORE VERIFYONLY      - verifies that the backup is readable by the RESTORE process

Syntax:

---------

RESTORE HEADERONLY FROM

DISK = 'C:\SQL2K8\Backups\Test.bak'

Go

RESTORE FILELISTONLY FROM

DISK = 'C:\SQL2K8\Backups\Test.bak'

Go

RESTORE VERIFYONLY FROM

DISK = 'C:\SQL2K8\Backups\Test.bak'

Go

RESTORE LABELONLY FROM

DISK = 'C:\SQL2K8\Backups\Test.bak'

The above commands gives you detailed information of what files are backed up, when they are backed up, backup media details and the validity of backup file weather the backup file is useful for restore or not. These commands has can be used along with other options like “FILE = ‘value’” and other options which will give more information.

Thanks