Learn more about SQL Server tools

 
 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

How to get the contents of a SQL Server backup file


     

Overview

The RESTORE HEADERONLY option allows you to see the backup header information for all backups for a particular backup device.  So in most cases each backup you create only has one backup stored in a physical file, so you will probably only see one header record, but if you had multiple backups in one file you would see the information for each backup. 

Explanation

The RESTORE HEADERONLY option can be simply issued as follows for a backup that exists on disk.


Get headeronly information from a full backup

T-SQL

RESTORE HEADERONLY FROM DISK = 'C:\AdventureWorks.BAK'
GO

The result set would like the following.  As you can see there is a lot of great information that is returned when using HEADERONLY.

ColumnName Value
BackupName NULL
BackupDescription NULL
BackupType 1
ExpirationDate NULL
Compressed 0
Position 1
DeviceType 2
UserName TESTServer1\DBA
ServerName TESTServer1
DatabaseName AdventureWorks
DatabaseVersion 611
DatabaseCreationDate 10/22/08 13:48
BackupSize 177324544
FirstLSN 414000000754800000
LastLSN 414000000758300000
CheckpointLSN 414000000754800000
DatabaseBackupLSN 0
BackupStartDate 3/19/09 12:02
BackupFinishDate 3/19/09 12:02
SortOrder 0
CodePage 0
UnicodeLocaleId 1033
UnicodeComparisonStyle 196608
CompatibilityLevel 90
SoftwareVendorId 4608
SoftwareVersionMajor 9
SoftwareVersionMinor 0
SoftwareVersionBuild 3077
MachineName TESTServer1
Flags 512
BindingID 459DDE25-B461-4CFD-B72E-0D4388F50331
RecoveryForkID E1BF182D-E21A-485A-9E2F-09E9C7DEC9D4
Collation Latin1_General_CS_AS
FamilyGUID E1BF182D-E21A-485A-9E2F-09E9C7DEC9D4
HasBulkLoggedData 0
IsSnapshot 0
IsReadOnly 0
IsSingleUser 0
HasBackupChecksums 0
IsDamaged 0
BeginsLogChain 0
HasIncompleteMetaData 0
IsForceOffline 0
IsCopyOnly 0
FirstRecoveryForkID E1BF182D-E21A-485A-9E2F-09E9C7DEC9D4
ForkPointLSN NULL
RecoveryModel FULL
DifferentialBaseLSN NULL
DifferentialBaseGUID NULL
BackupTypeDescription Database
BackupSetGUID 0C6D57F2-2EDB-4DEB-9C10-53C68578B046

If this backup file contained multiple backups you will get information for each backup that was in the file.


SQL Server Management Studio






More SQL Server Solutions




Post a comment or let the author know this tip helped you.

All comments are reviewed, so stay on subject or we may delete your comment.

*Name    *Email    Notify for updates 

Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, December 24, 2015 - 6:53:46 AM - Greg Robidoux Back To Top

If you use the RESTORE HEADERONLY for each file you can see if there is a gap in the LSNs.

Use this command on each log backup and check the FirstLSN and LastLSN to make sure there are not gaps.

Thanks
Greg


Thursday, December 24, 2015 - 6:48:08 AM - NAUSHAD ALAM Back To Top

Hi All,

I got a error message when i restore log file with no recovery through database "Unable to create restore plan due to break in the LSN chain" plz help me.

 

Thanks

Naushad 

 


Learn more about SQL Server tools