Tuesday, May 31, 2011

How to get version info from SQL backup (.bak) file


Get version info from SQL backup
use this little code to find the .bak file information
USE NIRDB
RESTORE HEADERONLY
FROM
DISK = N'D:\VSS\NIR.bak'WITH NOUNLOAD;
 Version number to SQL Server version, here's two:
611 = SQL Server 2005
655 = SQL Server 2008

RESTORE HEADERONLY - contains a list of backup header information for a backup device
RESTORE FILELISTONLY - contains a list of the data and log files contained in the backup
RESTORE LABELONLY - contains information about the backup media
Following are sample outputs from each of the commands.  These first three outputs show you the
complete output from running the commands against a backup file containing one full backup of the
AdventureWorks database.  As you can see there is a lot more information in the files that may or
may not be useful to you.
RESTORE HEADERONLY FROM DISK='C:\Backup\Adv_Full.bak'
BackupName AdventureWorks-Full Database Backup
BackupDescription NULL
BackupType 1
ExpirationDate NULL
Compressed 0
Position 1
DeviceType 2
UserName EDGENB2\Sysadmin
ServerName EDGENB2\TEST1
DatabaseName AdventureWorks
DatabaseVersion 611
DatabaseCreationDate 38985.72449
BackupSize 173091840
FirstLSN 41000000054400000
LastLSN 41000000056800000
CheckpointLSN 41000000054400000
DatabaseBackupLSN 41000000041600000
BackupStartDate 1/3/2007 8:15:41 PM
BackupFinishDate 1/3/2007 8:15:41 PM
SortOrder 52
CodePage 0
UnicodeLocaleId 1033
UnicodeComparisonStyle 196609
CompatibilityLevel 90
SoftwareVendorId 4608
SoftwareVersionMajor 9
SoftwareVersionMinor 0
SoftwareVersionBuild 1399
MachineName EDGENB2
Flags 512
BindingID 5956B629-86DF-4000-BAC0-52194A773D3B
RecoveryForkID B935AAC8-BB1A-4C10-AD0B-014DFEF2FC72
Collation SQL_Latin1_General_CP1_CI_AS
FamilyGUID B935AAC8-BB1A-4C10-AD0B-014DFEF2FC72
HasBulkLoggedData 0
IsSnapshot 0
IsReadOnly 0
IsSingleUser 0
HasBackupChecksums 0
IsDamaged 0
BeginsLogChain 0
HasIncompleteMetaData 0
IsForceOffline 0
IsCopyOnly 0
FirstRecoveryForkID B935AAC8-BB1A-4C10-AD0B-014DFEF2FC72
ForkPointLSN NULL
RecoveryModel FULL
DifferentialBaseLSN NULL
DifferentialBaseGUID NULL
BackupTypeDescription Database
BackupSetGUID 1389292F-F593-425D-BD36-325FCEA0E02A
RESTORE FILELISTONLY FROM DISK='C:\Backup\Adv_Full.bak'
LogicalName AdventureWorks_Data AdventureWorks_Log
PhysicalName C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\ AdventureWorks_Data.mdf C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\ AdventureWorks_Log.ldf
Type D L
FileGroupName PRIMARY NULL
Size 188678144 2097152
MaxSize 35184372080640 2199023255552
FileId 1 2
CreateLSN 0 0
DropLSN 0 0
UniqueId 94EDC99D-D0E0-4146-95DA-1756D6C92348 EB9DB2B3-BE70-4F76-8345-7FF07FB705C7
ReadOnlyLSN 0 0
ReadWriteLSN 0 0
BackupSizeInBytes 172163072 0
SourceBlockSize 512 512
FileGroupId 1 0
LogGroupGUID NULL NULL
DifferentialBaseLSN 41000000041600000 0
DifferentialBaseGUID 6493F201-EBBA-47DD-BBDA-83A2772A8DA3 00000000-0000-0000-0000-000000000000
IsReadOnly 0 0
IsPresent 1 1
RESTORE LABELONLY FROM DISK='C:\Backup\Adv_Full.bak'
MediaName NULL
MediaSetId 23979995-927B-4FEB-9B5E-8CF18356AB39
FamilyCount 1
FamilySequenceNumber 1
MediaFamilyId 86C7DF2E-0000-0000-0000-000000000000
MediaSequenceNumber 1
MediaLabelPresent 0
MediaDescription NULL
SoftwareName Microsoft SQL Server
SoftwareVendorId 4608
MediaDate 1/3/07 8:15 PM
MirrorCount 1
If we have a backup file that contains multiple backups, using the HEADERONLY option shows us
the information for each of the backups.  Following is a condensed view of the RESTORE
HEADERONLY output.  As you can see there are three backups in this file; one full backup and two
transaction log backups.  This information can be determined by the BackupType.
BackupName AdventureWorks-Full Database Backup AdventureWorks-Transaction Log Backup AdventureWorks-Transaction Log Backup
BackupDescription NULL NULL NULL
BackupType 1 2 2
Position 1 2 3
BackupSize 173091840 74752 8192
FirstLSN 41000000054400000 41000000054400000 41000000059200000
LastLSN 41000000056800000 41000000059200000 41000000059200000
CheckpointLSN 41000000054400000 41000000054400000 41000000054400000
DatabaseBackupLSN 41000000041600000 41000000054400000 41000000054400000
BackupStartDate 1/3/07 8:15 PM 1/3/07 8:39 PM 1/3/07 8:40 PM
BackupFinishDate 1/3/07 8:15 PM 1/3/07 8:39 PM 1/3/07 8:40 PM
BackupTypeDescription Database Transaction Log Transaction Log
BackupSetGUID 1389292F-F593-425D-BD36-325FCEA0E02A 1DAB6FAA-14AD-4C3C-8081-6A15CB170782 285DC2A1-1E89-44A5-B9ED-373821C94054
So how does this information help you restore your databases?
When your backup files contain multiple backups in one file you need to specify the position of the file
that you are restoring.  This option for the RESTORE command is FILE, but this number corresponds
to the Position value.  So if we want to restore these files using the RESTORE command we would
issue the following three commands one for each of the backups using the value that is in the Position
from the HEADERONLY output..
RESTORE DATABASE AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak' 
WITH FILE = 1NORECOVERY

RESTORE LOG AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak' 
WITH FILE = NORECOVERY

RESTORE LOG AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak' 
WITH FILE = 3RECOVERY

In addition to being able to restore multiple backups from one backup file, we can also use the output
from the FILELISTONLY to determine where the default locations will be for the data and log files. 
If you take a look at the output above from the FILELISTONLY command and look at the values in
the LogicalName and PhysicalName you will see the directory where the database was stored was
in the "C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\" directory.  If you
just do a RESTORE the data and log files will be created in this directory.  If the directory does not
exist or if you want to specify another directory or file name you need to use the WITH MOVE option
of the RESTORE command.  This can be done as follows:
RESTORE DATABASE AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak' 
WITH FILE = 
1
RECOVERY

MOVE 
'AdventureWorks_Data' TO 'J:\SQLdata\AdventureWorks_Data.mdf'
MOVE 
'AdventureWorks_Log' TO 'X:\SQLlog\AdventureWorks_Log.ldf'

it's a nice artical of MSSQL- tips, thanks for giving such a valuable information.

No comments :

Post a Comment