How to see the list of files in a SQL server backup file?

How to see the list of files in a SQL server backup file?

Today I was coding one application which will help us to restore and take back of our application’s databases.While coding the application I came across a situation, where I need to now the logical file name of the mdf and ldf files in the backup file.I googled a loot and finally I found a solution which I thought of sharing here for other who needs the same.

The TSQL command below will show the show the back up type and other details of the file.

RESTORE HEADERONLY FROM DISK='E:\xxxxxxxxxxxxx'

 

To retrieve the logical files name and other details use the below TSQL

RESTORE FILELISTONLY FROM DISK = 'E:\xxxxxxxxxxxxx' with file=1

In the baove example 'E:\xxxxxxxxxxxxx' can be replaced with your backup files and location

Reference

http://www.mssqltips.com/sqlservertutorial/109/sql-server-restore-filelistonly/

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300

http://blog.sqlauthority.com/2007/02/25/sql-server-restore-database-backup-using-sql-script-t-sql/
http://beyondrelational.com/blogs/madhivanan/archive/2011/04/18/identifying-the-backup-type-of-backup-file.aspx