Sunday, April 17, 2011

How to verifying SQL Server Backups before Restore the database

When you try to copy sql backup files from one server to another server always make sure to check with backup files to RESTORE VERIFYONLY Statement.


use master
go
RESTORE VERIFYONLY FROM 'E:\MSSQL\Backup\Testdb.BAK'
go

-- With Monitoring Options
use master
go
RESTORE VERIFYONLY FROM 'E:\MSSQL\Backup\Testdb.BAK', Status=10
go

  
Checks performed by RESTORE VERIFYONLY include:

 That the backup set is complete and all volumes are readable.
  • Some header fields of database pages, such as the page ID (as if it were about to write the data).
  • Checksum (if present on the media).
  • Checking for sufficient space on destination devices.

Please Note :

RESTORE VERIFYONLY does not work on a database snapshot. To verify a database snapshot before a revert operation, you can run DBCC CHECKDB.

1 comment:

Dinesh Vishe said...

RESTORE LABELONLY FROM DISK='g:\TEST.BAK'
--- MEDIA set,software name, software vendoreid

RESTORE FILELISTONLY FROM DISK='g:\TEST.BAK'
--- CHECK MDF ,NDF AND log files with size


RESTORE HEADERONLY FROM DISK='g:\TEST.BAK'
-- CHECK Backup present in compatibility level, software version major,software version major