Extracting a field from RESTORE HEADERONLY

Question

I'm trying to use 'RESTORE HEADERONLY' to get the date when the backup I'm about to restore was made.

The command:

RESTORE HEADERONLY FROM DISK = '<path to .bak file>'

works fine in Query Analyzer and gives a resultset with something like 50 columns.

The problem is actually accessing this from code.

I can get this into a temp table by declaring every single one of the 50:ish columns, inserting into it with exec and getting the value I want from there.

The problem is that I really want to avoid having to declare the entire resultset as a temp table as it seems like a very brittle solution if they ever add columns to it in future versions.

Is there any way to just get a single column out of this resultset without declaring all the columns?

Answer 1

This works for me.

SELECT BackupStartDate 
FROM OPENROWSET('SQLNCLI',
                'Server=MARTINPC\MSSQL2008;Trusted_Connection=yes;',
'SET NOCOUNT ON;SET FMTONLY OFF;EXEC(''
RESTORE HEADERONLY 
FROM DISK = ''''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\Backup\DB1.bak''''
'')'
) 

The ad hoc distributed queries Option needs to be enabled. Or if you don't want to do that you can set up a loopback linked server and use that instead.

EXEC sp_addlinkedserver @server = 'LOCALSERVER',  @srvproduct = '',
                        @provider = 'SQLOLEDB', @datasrc = @@servername

SELECT BackupStartDate 
FROM OPENQUERY(LOCALSERVER, 
               'SET FMTONLY OFF;
               EXEC(''
               RESTORE HEADERONLY 
               FROM DISK = ''''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\Backup\DB1.bak''''
'')')

Answer 2

Since you only asked about accessing the data from 'code' without specifying any details what sort of code, I hereby present the PowerShell solution:

Invoke-SQLcmd -Query "RESTORE HEADERONLY FROM DISK = 'R:\SQLFiles\MSSQL.MSSQLSERVER.Backup\Backup.bak'" | Select-Object MachineName,DatabaseName,HasBackupChecksums,BackupStartDate,BackupFinishDate

Answer 3

The old-fashioned way, for reference:

declare @backupFile varchar(max) = 'C:\backupfile.bak';
declare @dbName varchar(256);

-- THIS IS SPECIFIC TO SQL SERVER 2012
--
declare @headers table 
( 
    BackupName varchar(256),
    BackupDescription varchar(256),
    BackupType varchar(256),        
    ExpirationDate varchar(256),
    Compressed varchar(256),
    Position varchar(256),
    DeviceType varchar(256),        
    UserName varchar(256),
    ServerName varchar(256),
    DatabaseName varchar(256),
    DatabaseVersion varchar(256),        
    DatabaseCreationDate varchar(256),
    BackupSize varchar(256),
    FirstLSN varchar(256),
    LastLSN varchar(256),        
    CheckpointLSN varchar(256),
    DatabaseBackupLSN varchar(256),
    BackupStartDate varchar(256),
    BackupFinishDate varchar(256),        
    SortOrder varchar(256),
    CodePage varchar(256),
    UnicodeLocaleId varchar(256),
    UnicodeComparisonStyle varchar(256),        
    CompatibilityLevel varchar(256),
    SoftwareVendorId varchar(256),
    SoftwareVersionMajor varchar(256),        
    SoftwareVersionMinor varchar(256),
    SoftwareVersionBuild varchar(256),
    MachineName varchar(256),
    Flags varchar(256),        
    BindingID varchar(256),
    RecoveryForkID varchar(256),
    Collation varchar(256),
    FamilyGUID varchar(256),        
    HasBulkLoggedData varchar(256),
    IsSnapshot varchar(256),
    IsReadOnly varchar(256),
    IsSingleUser varchar(256),        
    HasBackupChecksums varchar(256),
    IsDamaged varchar(256),
    BeginsLogChain varchar(256),
    HasIncompleteMetaData varchar(256),        
    IsForceOffline varchar(256),
    IsCopyOnly varchar(256),
    FirstRecoveryForkID varchar(256),
    ForkPointLSN varchar(256),        
    RecoveryModel varchar(256),
    DifferentialBaseLSN varchar(256),
    DifferentialBaseGUID varchar(256),        
    BackupTypeDescription varchar(256),
    BackupSetGUID varchar(256),
    CompressedBackupSize varchar(256),        
    Containment varchar(256),
    --
    -- This field added to retain order by
    --
    Seq int NOT NULL identity(1,1)
); 

insert into @headers exec('restore headeronly from disk = '''+ @backupFile +'''');
select @dbName = DatabaseName from @headers;
select @dbName;

Answer 4

This is a version independent sp I wrote to get the backup date from a file.

It's tested for SQL 2008R2, 2012 and 2014.

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'spGetBackupDateFromFile')
    EXEC ('CREATE PROC dbo.spGetBackupDateFromFile AS SELECT ''stub version, to be replaced''')
GO
/*----------------------------------------------------------------------
                    spGetBackupDateFromFile
------------------------------------------------------------------------
Versie      : 1.0
Autheur     : Theo Ekelmans 
Datum       : 2016-03-31
Change      : Initial release 
------------------------------------------------------------------------*/
alter procedure dbo.spGetBackupDateFromFile(@BackupFile as varchar(1000), @DT as datetime output) as 

declare @BackupDT datetime
declare @sql varchar(8000)
declare @ProductVersion NVARCHAR(128)
declare @ProductVersionNumber TINYINT

SET @ProductVersion = CONVERT(NVARCHAR(128),SERVERPROPERTY('ProductVersion'))
SET @ProductVersionNumber = SUBSTRING(@ProductVersion, 1, (CHARINDEX('.', @ProductVersion) - 1))

if object_id('dbo.tblBackupHeader') is not null drop table dbo.tblBackupHeader

set @sql = ''

-- THIS IS GENERIC FOR SQL SERVER 2008R2, 2012 and 2014
if @ProductVersionNumber in(10, 11, 12)
set @sql = @sql +'
create table dbo.tblBackupHeader
( 
    BackupName varchar(256),
    BackupDescription varchar(256),
    BackupType varchar(256),        
    ExpirationDate varchar(256),
    Compressed varchar(256),
    Position varchar(256),
    DeviceType varchar(256),        
    UserName varchar(256),
    ServerName varchar(256),
    DatabaseName varchar(256),
    DatabaseVersion varchar(256),        
    DatabaseCreationDate varchar(256),
    BackupSize varchar(256),
    FirstLSN varchar(256),
    LastLSN varchar(256),        
    CheckpointLSN varchar(256),
    DatabaseBackupLSN varchar(256),
    BackupStartDate varchar(256),
    BackupFinishDate varchar(256),        
    SortOrder varchar(256),
    CodePage varchar(256),
    UnicodeLocaleId varchar(256),
    UnicodeComparisonStyle varchar(256),        
    CompatibilityLevel varchar(256),
    SoftwareVendorId varchar(256),
    SoftwareVersionMajor varchar(256),        
    SoftwareVersionMinor varchar(256),
    SoftwareVersionBuild varchar(256),
    MachineName varchar(256),
    Flags varchar(256),        
    BindingID varchar(256),
    RecoveryForkID varchar(256),
    Collation varchar(256),
    FamilyGUID varchar(256),        
    HasBulkLoggedData varchar(256),
    IsSnapshot varchar(256),
    IsReadOnly varchar(256),
    IsSingleUser varchar(256),        
    HasBackupChecksums varchar(256),
    IsDamaged varchar(256),
    BeginsLogChain varchar(256),
    HasIncompleteMetaData varchar(256),        
    IsForceOffline varchar(256),
    IsCopyOnly varchar(256),
    FirstRecoveryForkID varchar(256),
    ForkPointLSN varchar(256),        
    RecoveryModel varchar(256),
    DifferentialBaseLSN varchar(256),
    DifferentialBaseGUID varchar(256),        
    BackupTypeDescription varchar(256),
    BackupSetGUID varchar(256),
    CompressedBackupSize varchar(256),'

-- THIS IS SPECIFIC TO SQL SERVER 2012
if @ProductVersionNumber in(11)
set @sql = @sql +'
    Containment varchar(256),'


-- THIS IS SPECIFIC TO SQL SERVER 2014
if @ProductVersionNumber in(12)
set @sql = @sql +'
    Containment tinyint, 
    KeyAlgorithm nvarchar(32), 
    EncryptorThumbprint varbinary(20), 
    EncryptorType nvarchar(32),'


--All versions (This field added to retain order by)
set @sql = @sql +'
    Seq int NOT NULL identity(1,1)
); 
'
exec (@sql)


set @sql = 'restore headeronly from disk = '''+ @BackupFile +'''' 

insert into dbo.tblBackupHeader 
exec(@sql)

select @DT = BackupStartDate from dbo.tblBackupHeader 

if object_id('dbo.tblBackupHeader') is not null drop table dbo.tblBackupHeader

Answer 5

SQL Server is working fine on Linux ;-)

So, this is my own solution for filelistonly (working too for any of the auxiliary statements):

sqlcmd -U *** -P *** -Q "restore filelistonly FROM DISK = '/tmp/backup.bak';" -o /tmp/metadata.out
tr -s ' ' < /tmp/metadata.out | cut -d' ' -f1,2,3,4,5 | tail -n +3 | head -n -2

Answer 6

declare @mybackup sysname = 'F:\testbak\test.bak' declare @major int = CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)),4) as int) declare @build int = CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)),2) as int) declare @AcceptableDaysOld int = 7

IF OBJECT_ID('tempdb..#Header') IS NOT NULL DROP TABLE #Header

if ( @major >= 10 ) --must be equal or newer than 2008 to meet this criteria! create table #Header ( BackupName varchar(256), BackupDescription varchar(256), BackupType varchar(256),
ExpirationDate varchar(256), Compressed varchar(256), Position varchar(256), DeviceType varchar(256),
UserName varchar(256), ServerName varchar(256), DatabaseName varchar(256), DatabaseVersion varchar(256),
DatabaseCreationDate varchar(256), BackupSize varchar(256), FirstLSN varchar(256), LastLSN varchar(256),
CheckpointLSN varchar(256), DatabaseBackupLSN varchar(256), BackupStartDate varchar(256), BackupFinishDate varchar(256),
SortOrder varchar(256), CodePage varchar(256), UnicodeLocaleId varchar(256), UnicodeComparisonStyle varchar(256),
CompatibilityLevel varchar(256), SoftwareVendorId varchar(256), SoftwareVersionMajor varchar(256),
SoftwareVersionMinor varchar(256), SoftwareVersionBuild varchar(256), MachineName varchar(256), Flags varchar(256),
BindingID varchar(256), RecoveryForkID varchar(256), Collation varchar(256), FamilyGUID varchar(256),
HasBulkLoggedData varchar(256), IsSnapshot varchar(256), IsReadOnly varchar(256), IsSingleUser varchar(256),
HasBackupChecksums varchar(256), IsDamaged varchar(256), BeginsLogChain varchar(256), HasIncompleteMetaData varchar(256),
IsForceOffline varchar(256), IsCopyOnly varchar(256), FirstRecoveryForkID varchar(256), ForkPointLSN varchar(256),
RecoveryModel varchar(256), DifferentialBaseLSN varchar(256), DifferentialBaseGUID varchar(256),
BackupTypeDescription varchar(256), BackupSetGUID varchar(256), CompressedBackupSize varchar(256) )

if ( @major >= 11 ) --ADD TO ACCOMODATE SQL 2012 ALTER TABLE #Header add Containment varchar(256)

if ( @major = 12 and @build >= 4100 ) OR (@major >= 13) --ADD TO ACCOMODATE SQL 2014 SP1 through SQL 2019 ALTER TABLE #Header add KeyAlgorithm nvarchar(32), EncryptorThumbprint varbinary(20), EncryptorType nvarchar(32)

--All versions (This field added to retain order by) ALTER TABLE #Header add Seq int NOT NULL identity(1,1) insert into #Header EXEC('RESTORE HEADERONLY FROM DISK=''' +@mybackup+ '''')

IF (select TOP 1 BackupStartDate from #Header order by BackupStartDate DESC) < GETDATE()-@AcceptableDaysOld print 'This is an old backup!' ELSE print 'This is a recent backup'

IF OBJECT_ID('tempdb..#Header') IS NOT NULL DROP TABLE #Header