Determining the contents of a backup file
Posted by decipherinfosys on December 12, 2008
The RESTORE command in SQL Server allows us to do a lot of things:
a) Obviously, restore a database completely from a full database backup or do a partial restore or restore a txn log of the database.
b) It also allows us to do a point in time restore by using database snapshot.
c) It also allows us to restore specific files, filegroups or even pages to a database.
Chances are that you are already aware of most of these options. Some of the lesser used options for the RESTORE command are restoring the master key from a backup file or restoring information about the contents of a backup file. Without restoring a backup, one can use these options to find out the contents of the backups. There are three such commands that are provided in SQL Server:
1) RESTORE FILELISTONLY,
2) RESTORE LABELONLY, and
3) RESTORE HEADERONLY.
The BOL contains detailed information on those so there is no point in mentioning those over here. You can also access those in the online version of the BOL over here.
So, how is this useful? This especially becomes useful if you or someone else has been doing backups to a single file – in that case, when you are restoring from that backup and you need to go to a particular file, then you can find that information and specify that position of the file in the RESTORE command. In addition, these also provide you with the information on the logical names and the physical file locations for those files in the backup. If then you want to restore them to a different location, all you need to do is use the MOVE option in the RESTORE command to move those files to the new folder location. All this is also accessible through the GUI and these are the actual commands that get fired when you are looking at the data values through the GUI. Having a knowledge of the commands also helps in case you sometime need to a move from one instance to another and have been given a lot of backups in order to make that happen – using commands, it can be easily scripted – with GUI, it will take a lot longer.