2017-02-22

UnPivot The Output Of RESTORE HEADERONLY

Dave Mason - SQL Server UnPivot RESTORE HEADERONLY Output

Today's less-than-ugent challenge was to un-pivot the output of RESTORE HEADERONLY. I thought for certain someone else, somewhere, at at some time must have wanted to do the same thing. So I asked the Twitterverse, but no one responded. I guess I'll have to make do myself without the easy button. No worries, though. We can do this!

In short, I wanted to take this...

BackupNameBackupDescriptionBackupTypeExpirationDateCompressedPositionDeviceTypeUserNameServerNameDatabaseNameDatabaseVersionDatabaseCreationDateBackupSizeFirstLSNLastLSNCheckpointLSNDatabaseBackupLSNBackupStartDateBackupFinishDateSortOrderCodePageUnicodeLocaleIdUnicodeComparisonStyleCompatibilityLevelSoftwareVendorIdSoftwareVersionMajorSoftwareVersionMinorSoftwareVersionBuildMachineNameFlagsBindingIDRecoveryForkIDCollationFamilyGUIDHasBulkLoggedDataIsSnapshotIsReadOnlyIsSingleUserHasBackupChecksumsIsDamagedBeginsLogChainHasIncompleteMetaDataIsForceOfflineIsCopyOnlyFirstRecoveryForkIDForkPointLSNRecoveryModelDifferentialBaseLSNDifferentialBaseGUIDBackupTypeDescriptionBackupSetGUIDCompressedBackupSizeContainment
SomeApp9_DatabaseTemplate-Full Database BackupNULL1NULL012SomeVendor\SRHaddenSomeSqlHost\SQL2008R2SomeApp9_DatabaseTemplate6612014-01-02 09:13:15.00031781888109930000004631000361099300000046480000110993000000463100036109930000004138002562015-09-14 13:18:44.0002015-09-14 13:18:46.000520103319660990460810501617SomeSqlHost51262542A27-94A4-4010-905A-32AEFA4016ABFC58F82C-0D2C-4D7F-9225-3DF679BBF636SQL_Latin1_General_CP1_CI_AS25E2953C-BAAA-4A41-9E08-B0E4D82B70880000000000FC58F82C-0D2C-4D7F-9225-3DF679BBF636NULLSIMPLENULLNULLDatabaseAC2D9342-692E-4D05-B321-16274E6040C3317818880

...and "flip it" to this:

FieldValue
BackupNameSomeApp9_DatabaseTemplate-Full Database Backup
BackupDescriptionNULL
BackupType1
ExpirationDateNULL
Compressed0
Position1
DeviceType2
UserNameSomeVendor\SRHadden
ServerNameSomeSqlHost\SQL2008R2
DatabaseNameSomeApp9_DatabaseTemplate
DatabaseVersion661
DatabaseCreationDateJan 2 2014 9:13AM
BackupSize31781888
FirstLSN10993000000463100036
LastLSN10993000000464800001
CheckpointLSN10993000000463100036
DatabaseBackupLSN10993000000413800256
BackupStartDateSep 14 2015 1:18PM
BackupFinishDateSep 14 2015 1:18PM
SortOrder52
CodePage0
UnicodeLocaleId1033
UnicodeComparisonStyle196609
CompatibilityLevel90
SoftwareVendorId4608
SoftwareVersionMajor10
SoftwareVersionMinor50
SoftwareVersionBuild1617
MachineNameSomeSqlHost
Flags512
BindingID62542A27-94A4-4010-905A-32AEFA4016AB
RecoveryForkIDFC58F82C-0D2C-4D7F-9225-3DF679BBF636
CollationSQL_Latin1_General_CP1_CI_AS
FamilyGUID25E2953C-BAAA-4A41-9E08-B0E4D82B7088
HasBulkLoggedData0
IsSnapshot0
IsReadOnly0
IsSingleUser0
HasBackupChecksums0
IsDamaged0
BeginsLogChain0
HasIncompleteMetaData0
IsForceOffline0
IsCopyOnly0
FirstRecoveryForkIDFC58F82C-0D2C-4D7F-9225-3DF679BBF636
ForkPointLSNNULL
RecoveryModelSIMPLE
DifferentialBaseLSNNULL
DifferentialBaseGUIDNULL
BackupTypeDescriptionDatabase
BackupSetGUIDAC2D9342-692E-4D05-B321-16274E6040C3
CompressedBackupSize31781888
containment0

My first thought was to try forcing the RESTORE HEADERONLY output into a temp table. I felt confident this would work:

SELECT * INTO #TempTable FROM OPENROWSET('SQLNCLI', 'Server=.\SQL2014;Trusted_Connection=yes;',
     'EXEC (''RESTORE HEADERONLY FROM DISK = N''''D:\Backup\SomeApp9_Template.bak'''''')');
But I was wrong, as this error message clearly shows:
Msg 11527, Level 16, State 1, Procedure sp_describe_first_result_set, Line 9
The metadata could not be determined because statement 'RESTORE HEADERONLY FROM DISK = N'D:\Backup\SomeApp9_Template.bak'' does not support metadata discovery.


Surely something like this will work:

INSERT INTO #TempTable 
EXEC ('RESTORE HEADERONLY FROM DISK = N''D:\Backup\SomeApp9_Template.bak'' ')


But that means I have to define a temp table that matches the output of RESTORE HEADERONLY. Damn. That's a wide table I really didn't want to have to create. But it looks like I have to. <Grumble/> You know, I may want to do this again someday for another backup. So instead of defining a temp table, I'll create a reusable TABLE TYPE.

CREATE TYPE dbo.Header_Only AS TABLE (
    BackupName nvarchar(128),
    BackupDescription nvarchar(255),
    BackupType smallint,
    ExpirationDate datetime,
    Compressed BIT,
    Position smallint,
    DeviceType tinyint,
    UserName nvarchar(128),
    ServerName nvarchar(128),
    DatabaseName nvarchar(128),
    DatabaseVersion int,
    DatabaseCreationDate datetime,
    BackupSize numeric(20,0),
    FirstLSN numeric(25,0),
    LastLSN numeric(25,0),
    CheckpointLSN numeric(25,0),
    DatabaseBackupLSN numeric(25,0),
    BackupStartDate datetime,
    BackupFinishDate datetime,
    SortOrder smallint,
    CodePage smallint,
    UnicodeLocaleId int,
    UnicodeComparisonStyle int,
    CompatibilityLevel tinyint,
    SoftwareVendorId int,
    SoftwareVersionMajor int,
    SoftwareVersionMinor int,
    SoftwareVersionBuild int,
    MachineName nvarchar(128),
    Flags int,
    BindingID uniqueidentifier,
    RecoveryForkID uniqueidentifier,
    Collation nvarchar(128),
    FamilyGUID uniqueidentifier,
    HasBulkLoggedData bit,
    IsSnapshot bit,
    IsReadOnly bit,
    IsSingleUser bit,
    HasBackupChecksums bit,
    IsDamaged bit,
    BeginsLogChain bit,
    HasIncompleteMetaData bit,
    IsForceOffline bit,
    IsCopyOnly bit,
    FirstRecoveryForkID uniqueidentifier,
    ForkPointLSN numeric(25,0) NULL,
    RecoveryModel nvarchar(60),
    DifferentialBaseLSN numeric(25,0) NULL,
    DifferentialBaseGUID uniqueidentifier,
    BackupTypeDescription nvarchar(60),
    BackupSetGUID uniqueidentifier NULL,
    CompressedBackupSize bigint,
    containment tinyint not NULL
)
GO

Now that I've got the TABLE TYPE, I'll use it to create a temp table, and insert the output of the RESTORE HEADERONLY statement into it via EXEC and a "RESTORE" string. So far, so good.

DECLARE @HO AS dbo.Header_Only;

SELECT *
INTO #HO
FROM @HO
WHERE 1 = 2

INSERT INTO #HO
EXEC ('RESTORE HEADERONLY FROM DISK = N''D:\Backup\SomeApp9_Template.bak'' ')

Next, I'll build a dynamic T-SQL query by iterating through the column names of the temp table (well, actually of the table type). The end result is a bunch of queries that select a static string (field name) and a single column (field value) from the temp table, UNION'ed together. The last "UNION ALL" is trimmed from the string. And the string is executed.

DECLARE @TSql VARCHAR(MAX) = '';
SELECT 
 @TSql = @TSql + 'SELECT ''' + c.name + ''' AS [Field], CAST([' + c.name + '] AS VARCHAR(MAX)) AS [Value] FROM #HO UNION ALL' + CHAR(13) + CHAR(10)
FROM sys.table_types t
JOIN sys.columns c 
 on c.object_id = t.type_table_object_id
WHERE t.name = 'Header_Only'
ORDER BY c.column_id

SET @TSql = LEFT(@TSql, LEN(@TSql) - 11) + CHAR(13) + CHAR(10)

EXEC (@TSql)

DROP TABLE #HO

You may be laughing and thinking "Gee, that's a lot of work." And you'd be right. But five years from now when I need to do this again, I'll have the last laugh. Bwah ha ha ha!


SHARE