You are here:Home » tsql » Database Backup files Verification and Details - SQL Server

Database Backup files Verification and Details - SQL Server

As a best practice, the DBA needs to verify each database backups are properly done or not, also make sure the backups are OK then is readable and can be restored. Because so many databases are scheduled as FULL and differential backups weekly/daily and transaction log backups on every hour or whatever as per requirement performing to even data loss.

You can read my earlier articles of script to automated all types of database backup and split database backup to multiple files.

How can we verify the backup files?
SQL Server provides VERIFYONLY clause and we can use it with Restore command. Please see the below details for the same as how it works.

#1. VERIFYONLY
Verify database backup integrity and checking backups are corrupted or not. Per SQL Book online, Verifies the backup but does not restore it, and checks to see that the backup set is complete and the entire backup is readable. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. In Microsoft SQL Server, RESTORE VERIFYONLY has been enhanced to do additional checking on the data to increase the probability of detecting errors. The goal is to be as close to an actual restore operation as practical. For more information, see the Remarks.
If the backup is valid, the SQL Server Database Engine returns a success message. Let us run the query to verify FULL, Differential and Transactional log backups and will see the output come out of it.
RESTORE VERIFYONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_182654.bak'
GO
RESTORE VERIFYONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_183348.bak'
GO
RESTORE VERIFYONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_183843.trn'
GO

How can we get the backup files details?
Using HEADERONLY with Restore command we have details for database backup files. Let us run the query to verify FULL, Differential and Transactional log backups and will see the output come out of it.

#2. HEADERONLY
Per SQL Book online, Returns a result set containing all the backup header information for all backup set on a particular backup device.
RESTORE HEADERONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_182654.bak'
GO
RESTORE HEADERONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_183348.bak'
GO
RESTORE HEADERONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_183843.trn'
GO

Here another command FILELISTONLY which will the logical file and physical file details of backup files.

#3. FILELISTONLY
Per SQL Book online, Returns a result set containing a list of the database and log files contained in the backup set.
RESTORE FILELISTONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_182654.bak'
GO
RESTORE FILELISTONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_183348.bak'
GO
RESTORE FILELISTONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_183843.trn'
GO

What you are performing an activity with backups?