Yesterday, one of my friends asked me that how can he know how much space is required if he restores the database from backup file? I helped him using the RESTORE statement with its associated set of auxiliary statements:

• RESTORE LABELONLY
• RESTORE HEADERONLY
• RESTORE FILELISTONLY
• RESTORE REWINDONLY and
• RESTORE VERIFYONLY

Importance of Database backup:

Database backup needs to be taken for the following most important reasons from the perspective of the DBA:

• You could be fired (or face some other serious career problem) if backup is not available if something goes wrong.
• Beyond being a cause for firing, a serious enough backup failure may cost huge for your company & its life. We couldn’t imagine company being totally unavailable for a day.
• There are certain types of data that needs to be backed up because maintaining the records are required by law.
• Without backup, you are in trouble if you face data or page corruption

As you know, you can use BACKUP command to backup the database.

RESTORE statement:

Without going much in details regarding database Backup, let me throw some lights on how can you get invaluable information without actual restore of the database. You can do this by using RESTORE statement’s auxiliary statements/commands to answer the following questions:

• What’s in the backup?
• How much hard disk space is required to restore the backup?
• When it was taken?
• Is the backup damaged?
• What is the type of the backup?
• And many more…

Restore command helps to perform the following restore scenarios:

• Restore an entire database from a full database backup (a complete restore).
• Restore part of a database (a partial restore).
• Restore specific files or file-groups to a database (a file restore).
• Restore specific pages to a database (a page restore).
• Restore a transaction log onto a database (a transaction log restore).
• Revert a database to the point in time captured by a database snapshot.

 

For this post, to display sample output, I have taken FULL backup of my database which has 4 files and 3 file groups as below:

RESTORE LABELONLY:

It returns a result set containing information about the backup media identified by the given backup device.
 
We can use the command as below:

RESTORE LABELONLY FROM DISK = 'D:\temp\TestingDatabaseBackup.bak'

 
It will return information like Media Name, Media Description, Media Date (when backup file is created), Is it Compressed backup or not, SoftwareVendorId (For SQL Server, this number is 4608 (or hexadecimal 0x1200)).
 
Command result in my case is as follows:

 

MediaName	TestingDBMedia
MediaSetId	C8775310-4440-4A58-9061-8CDCB9138B13
FamilyCount	1
FamilySequenceNumber	1
MediaFamilyId	8EEB8227-0000-0000-0000-000000000000
MediaSequenceNumber	1
MediaLabelPresent	0
MediaDescription	TestingDBMedia description
SoftwareName	Microsoft SQL Server
SoftwareVendorId	4608
MediaDate	5/31/2012 17:31:42
MirrorCount	1
IsCompressed	1

 

RESTORE HEADERONLY:

It returns a result set containing all the backup header information for all backup sets on a particular backup device.
 
We can use the command as below:

RESTORE HEADERONLY FROM DISK = 'D:\temp\TestingDatabaseBackup.bak' 

 
It will return lots of useful information. Few are self explanatory like - Backup Name, Backup Description, Expiration Date, User Name, Server Name, Database Name, Recovery Model, Compatibility Level etc. BackupType is one of the 7 different backup types, but identified by number, not description. It provides lot of information, so you need to focus on the parts that are immediately important to you and ignore the rest.
Command also returns the bit fields that are almost self-explanatory. HasBulkLoggedData, IsSnapshot, IsReadOnly, IsSingleUser, HasBackupChecksums (very useful for verifying the backup), IsDamaged (good to know), BeginsLogChain, HasIncompleteMetaData (only set for tail log backups), IsForceOffline, and IsCopyOnly.
 
Command result in my case is as follows:

BackupName	TestingDatabase-Full Database Backup
BackupDescription	TestingDatabase Full Backup on TestingDBMedia
BackupType	1
ExpirationDate	NULL
Compressed	1
Position	1
DeviceType	2
UserName	sa
ServerName	HD_TestServer
DatabaseName	TestingDatabase
DatabaseVersion	661
DatabaseCreationDate	5/29/2012 18:10:08
BackupSize	2179072
FirstLSN	36000000008900000
LastLSN	36000000010500000
CheckpointLSN	36000000008900000
DatabaseBackupLSN	36000000003200000
BackupStartDate	5/31/2012 17:31:42
BackupFinishDate	5/31/2012 17:31:42
SortOrder	52
CodePage	0
UnicodeLocaleId	1033
UnicodeComparisonStyle	196609
CompatibilityLevel	100
SoftwareVendorId	4608
SoftwareVersionMajor	10
SoftwareVersionMinor	50
SoftwareVersionBuild	1600
MachineName	HD_Desktop
Flags	528
BindingID	A8EFED62-CF38-44C5-AFF4-F994628C47E4
RecoveryForkID	35B7891F-3C64-49DE-9CEA-1053E18B5DF0
Collation	SQL_Latin1_General_CP1_CI_AS
FamilyGUID	35B7891F-3C64-49DE-9CEA-1053E18B5DF0
HasBulkLoggedData	0
IsSnapshot	0
IsReadOnly	0
IsSingleUser	0
HasBackupChecksums	1
IsDamaged	0
BeginsLogChain	0
HasIncompleteMetaData	0
IsForceOffline	0
IsCopyOnly	0
FirstRecoveryForkID	35B7891F-3C64-49DE-9CEA-1053E18B5DF0
ForkPointLSN	NULL
RecoveryModel	FULL
DifferentialBaseLSN	NULL
DifferentialBaseGUID	NULL
BackupTypeDescription	Database
BackupSetGUID	0D04D7E9-CD36-4FAE-8FDD-BD2833FB549D
CompressedBackupSize	398917

 

RESTORE FILELISTONLY:

It returns a result set containing a list of the database and log files contained in the backup set.
 
This is one of my favorite command.
 
We can execute the command as below:

RESTORE FILELISTONLY FROM DISK = 'D:\temp\TestingDatabaseBackup.bak' 

 
You can get lots of information about a database backup like how may files, file groups were present when backup was taken. Logical and physical names is necessary to use the MOVE statement.
Type shows ‘D’ for data, ‘L’ for log, ‘F’ for Full text catalog. Size is in Bytes.
 
You can SUM all the files ‘Size’ column value and get idea what hard disk space should be required to restore the database. For example:  6291456 bytes = 6 MB, 2097152 = 2 MB. BackupSizeInBytes column represents ‘Size of the backup for this file in bytes’.
 
Command result in my case is as follows:

LogicalName	Test_Default	Test_Data	Test_Index	Test_Log
PhysicalName	C:\DATA\Test_Default.mdf	C:\DATA\Test_Data.ndf	C:\DATA\Test_Index.ndf	C:\DATA\Test_Log.ldf
Type	D	D	D	L
FileGroupName	PRIMARY	DataGroup	IndexGroup	NULL
Size	6291456	2097152	2097152	1048576
MaxSize	35184372080640	35184372080640	35184372080640	2199023255552
FileId	1	3	4	2
CreateLSN	0	33000000005700000	33000000008400000	0
DropLSN	0	0	0	0
UniqueId	4E4C3234-CDF4-4791-8034-204EAD8B0F19	2FBBDCE6-D95F-49B1-A43D-231C5C097274	29C12A4C-7988-4929-8876-BB9FA85D6D27	1A271C5D-A311-48D1-9295-1B8A86996A6F
ReadOnlyLSN	0	0	0	0
ReadWriteLSN	0	0	0	0
BackupSizeInBytes	1638400	196608	65536	0
SourceBlockSize	512	512	512	512
FileGroupId	1	2	3	0
LogGroupGUID	NULL	NULL	NULL	NULL
DifferentialBaseLSN	36000000003200000	36000000003200000	36000000003200000	0
DifferentialBaseGUID	05932DE1-3892-48B2-B60E-3D7418E8D78E	05932DE1-3892-48B2-B60E-3D7418E8D78E	05932DE1-3892-48B2-B60E-3D7418E8D78E	00000000-0000-0000-0000-000000000000
IsReadOnly	0	0	0	0
IsPresent	1	1	1	1
TDEThumbprint	NULL	NULL	NULL	NULL

 

RESTORE VERIFYONLY:

It 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. If the backup is valid, the SQL Server Database Engine returns a success message.
 
One more from my favorite list.
 
RESTORE VERIFYONLY performs following:

• 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.

We can execute the command as below:

RESTORE VERIFYONLY FROM DISK = 'D:\temp\TestingDatabaseBackup.bak' 

 
Command result in my case is as follows:
“
The backup set on file 1 is valid.
”
 

RESTORE REWINDONLY:

It rewinds and closes specified tape devices that were left open by BACKUP or RESTORE statements executed with the NOREWIND option.
This command is supported only for tape devices.
 
We can execute the command as below:

RESTORE REWINDONLY FROM DISK = 'D:\temp\TestingDatabaseBackup.bak' 

 
Command result in my case is as follows:
“
Msg 3216, Level 16, State 1, Line 2
RESTORE REWINDONLY is only applicable to tape devices.
Msg 3013, Level 16, State 1, Line 2
RESTORE REWINDONLY is terminating abnormally.
”
 

Conclusion:

In conclusion, when you are faced with that mysterious backup from some unknown source, or you’re stuck in the emergency and the obscure file names aren’t helping you recover the database, don’t be panic. You can refer LABELONLY, HEADERONLY, and FILELISTONLY to put together the information you need and check/verify the database using VERIFYONLY.
The best information is going to come out of HEADERONLY, FILELISTONLY and VERIFYONLY and with these you should be able to figure out what is needed to restore about any database from the backup media you have available.
 
 

References:

RESTORE HEADERONLY (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms178536
RESTORE FILELISTONLY (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms173778
RESTORE LABELONLY (Transact-SQL):  http://msdn.microsoft.com/en-us/library/ms186268
RESTORE VERIFYONLY (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms188902
RESTORE REWINDONLY (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms186851