Thursday, 23 May 2013

Get Information About Your Backups

Retrieving backup information

We’re going to look at three different commands: LABELONLYHEADERONLYFILELISTONLY. These three read the information stored about the backup in the backup file and display it all for you.

LABELONLY

RESTORE LABELONLY FROM DISK = 'd:\bu\mm.bak' ;
The ouptut from my backup file is as follows:
MediaNameNULL
MediaSetIdC78024B4-4FD7-4141-98F4-685C8B127B14
FamilyCount1
FamilySequenceNumber1
MediaFamilyIdB61166AA-0000-0000-0000-000000000000
MediaSequenceNumber1
MediaLabelPresent0
MediaDescriptionNULL
SoftwareNameMicrosoft SQL Server
SoftwareVendorId4608
MediaDate2012-02-01 14:31:09.000
MirrorCount1
IsCompressed0

HEADERONLY

The amount of information you get when you read the entire header is fairly insane. You retrieve it the same way as you would retrieve the LABELONLY data:
RESTORE HEADERONLY FROM DISK = 'd:\bu\mm.bak' ;
This command outputs a lot of information, listed here:
BackupNameMovieManagement
BackupDescriptionDaily Full Backup
BackupType1
ExpirationDateNULL
Compressed0
Position1
DeviceType2
UserNameNEVERNEVER\Grant
ServerNameGRANT-RED1\GFR1
DatabaseNameMovieManagement
DatabaseVersion661
DatabaseCreationDate2011-04-19 13:24:09.000
BackupSize63001600
FirstLSN89000000041600037
LastLSN89000000043200001
CheckpointLSN89000000041600037
DatabaseBackupLSN89000000039100037
BackupStartDate2012-02-13 10:08:21.000
BackupFinishDate2012-02-13 10:08:22.000
SortOrder52
CodePage0
UnicodeLocaleId1033
UnicodeComparisonStyle196609
CompatibilityLevel100
SoftwareVendorId4608
SoftwareVersionMajor10
SoftwareVersionMinor50
SoftwareVersionBuild1797
MachineNameGRANT-RED1
Flags512
BindingID62BB8F51-371F-4F17-8F43-7644232F8932
RecoveryForkID872E823E-2130-4BF1-8B04-054B295F0E08
CollationSQL_Latin1_General_CP1_CI_AS
FamilyGUIDEEF9BC86-F81B-4CF0-A8E2-462C04E342E0
HasBulkLoggedData0
IsSnapshot0
IsReadOnly0
IsSingleUser0
HasBackupChecksums0
IsDamaged0
BeginsLogChain0
HasIncompleteMetaData0
IsForceOffline0
IsCopyOnly0
FirstRecoveryForkID872E823E-2130-4BF1-8B04-054B295F0E08
ForkPointLSNNULL
RecoveryModelSIMPLE
DifferentialBaseLSNNULL
DifferentialBaseGUIDNULL
BackupTypeDescriptionDatabase
BackupSetGUID56E75391-D781-4394-AE0A-979D85F7A0D9
CompressedBackupSize63001600


FILELISTONLY

Now you can find out lots of information about a backup, but do you have enough to run a restore operation based on the information you’ve collected? Short answer: nope. You still don’t have any clue about how the database itself was distributed, and you need that if you’re running a full restore or any kind of file/filegroup restore. If you’re replacing an existing database with a backup taken from that database, you won’t need this. But if you’re restoring to servers with different disk layouts, you just might want to know how many files you have and where they’re stored. To get that information, you need a RESTORE FILELIST ONLY, which you run like this:
RESTORE FILELISTONLY FROM DISK = 'd:\bu\mm.bak'
The results come back like this:
LogicalNameMovieManagementMovieManagement_log
PhysicalNameC:\Program Files\Microsoft SQL Server\MSSQL10_50.GFR1\MSSQL\
DATA\MovieManagement.mdf
C:\Program Files\Microsoft SQL Server\MSSQL10_50.GFR1\MSSQL\
DATA\MovieManagement_log.LDF
TypeDL
FileGroupNamePRIMARYNULL
Size631767043604480
MaxSize351843720806402199023255552
FileId12
CreateLSN00
DropLSN00
UniqueIdF50B75A6-C564-4E44-A117-D7AF48B637BFF1C86747-7D4F-4E80-9C28-50460B38C537
ReadOnlyLSN00
ReadWriteLSN00
BackupSizeInBytes624558080
SourceBlockSize512512
FileGroupId10
LogGroupGUIDNULLNULL
DifferentialBaseLSN890000000391000370
DifferentialBaseGUIDBB5D1D2C-75AD-4CD6-B20F-70CEBABC017600000000-0000-0000-0000-000000000000
IsReadOnly00
IsPresent11
TDEThumbprintNULLNULL




No comments:

Post a Comment