Thursday, January 5, 2012

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

No comments:

Post a Comment