Get Information About Your Backups

By -

Retrieving backup information

We’re going to look at three different commands: LABELONLY, HEADERONLY, FILELISTONLY. 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




Comments

Post a Comment

Popular posts from this blog

Index Clean-Up Scripts

By -
dropping un-used ones, adding missing ones, and fine-tuning the ones I already have. I thought I’d share some of the scripts I’ve been using to accomplish this. Un-Used Indexes Script Declare @dbid int     , @dbName varchar(100);   Select @dbid = DB_ID()     , @dbName = DB_Name();   With partitionCTE (object_id, index_id, row_count, partition_count)  As (     Select [object_id]         , index_id         , Sum([rows]) As 'row_count'         , Count(partition_id) As 'partition_count'     From sys.partitions     Group By [object_id]         , index_id )    Select Object_Name(i.[object_id]) as objectName         , i.name         , Case              When i.is_unique = 1          ...
Read more

forgot sa password and no logins are added

By -
Image
Stop the SQL services Edit the properties of the SQL Service Change the startup parameters of the SQL service by adding a  –m;  in front of the existing parameters Start the SQL services. These are now running in Single User Mode. Start CMD on tthe SQL server Start the SQLCMD command. Now we create a new user. Enter following commands CREATE LOGIN recovery WITH PASSWORD = ‘TopSecret 1′ (Remember SQL server has default strong password policy Go Now this user is created Now we grant the user a SYSADMIN roles using the same SQLCMD window. sp_addsrvrolemember ‘recovery’, ‘sysadmin’ go
Read more

The SQL Server DBA’s Guide to Teradata

By -
Being a long-time devotee of all things Microsoft SQL Server, I had quite a bit of learning to do to get up to speed on our new Teradata appliance. This guide attempts to distill some of what I have learned. I will primarily focus on what I have found most lacking: examples of how to convert SQL Server T-SQL to Teradata SQL. This guide will use the vendor-supplied sample databases:  SQL Server’s AdventureWorksDW2012 , and the  Teradata’s Express 14.0 Retail database . Also, while this is probably self explanatory, the “mufford” object referenced in the Teradata examples is simply my own personal sandbox. You’ll need to replace any “mufford” reference to whatever your development sandbox may be named. Release History: 2012-08-17 – Added UPDATE examples; reformatted 2012-08-12 – Added date examples 2012-08-08 – Initial Release SELECT Statements Simple SELECT SQL Server: Best practice is to qualify the table schema; qualifying the database is optional unless ...
Read more