Know the type of bakup and database without restoring it to your database

I was killing out time with one of my SQL DBA community, while juggling various queries, a particular question squeeze my interest lime, the question was

“How to find which database and type of bakup it is without restoring the bak file”  

The answer to this is very simple, lets try to see with an example, I have a bakup file at “D:\SQL Server\myBakupFile.bak” lets see how we can get the detail of bakup type and database name without restoring it.

Step1: Verify whether the bakup file is valid for restore.

RESTORE VERIFYONLY FROM

disk = 'D:\SQL Server\myBakupFile.bak'

The backup set on file 1 is valid.

Step2: Get the header info of the bak file.

RESTORE HEADERONLY FROM 

disk = 'D:\SQL Server\myBakupFile.bak'

BackupName                  DatabaseName 

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

TestDB-Full Database Backup TestDB       

(1 row(s) affected)

That’s all!!! :D pretty simple

Conclusion: Use HEADERONLY option of Restore to get the bak file information without actually restoring the bakup file to the server.

Pretty simple you are, anyone who came closer to you, fall in love with you, SQL Server :)