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 |
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 |
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 = 1, NORECOVERYRESTORE LOG AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak'
WITH FILE = 2 , NORECOVERYRESTORE LOG AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak'
WITH FILE = 3, RECOVERYIn 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