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?

Martin Smith · Accepted Answer · 2012-02-08T11:14:15.230

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''''
'')')

score 10 · Answer 2 · answered Mar 31 '16 at 10:50

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

score 9 · Answer 3 · edited Sep 24 '15 at 07:00

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;

score 8 · Answer 4 · edited Mar 31 '16 at 10:34

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

score 0 · Answer 5 · answered Jul 23 '21 at 13:09

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

Marcello Miorelli · Answer 6 · 2024-12-30T17:31:49.487

In this answer, to get the data I need from the restore headeronly command, and not creating a table for that, I use openrowset, and I have the path as parameter, so it can be changed.

Note that I use the character nchar(39) instead of apostrophes (').

I use WITH RESULT SETS in the code and I am running this on sql server 2016 and sql server 2019.

The script comes out like this:


Declare @path VARCHAR(260)
Declare @sql nvarchar(max)
Set @path='C:\backups\DIFF\my_server_AdventureWorks_diff.bak'

-- check contents of restore headeronly
-- marcello miorelli
select @sql=N'
select 
 a.ServerName
,a.DatabaseName
,a.BackupStartDate
,a.BackupFinishDate
,a.HasBackupChecksums
,a.IsCopyOnly
,a.Compressed
,a.CompressedBAckupSize
,a.BackupSize
,a.EncryptorThumbprint
,a.KeyAlgorithm
,a.EncryptorType
from openrowset(''SQLNCLI'',''Server=(local);Trusted_Connection=yes;'',
''SET FMTONLY OFF;
 EXEC(''''
 RESTORE HEADERONLY 
 FROM DISK = '''''''+nchar(39) + @path + nchar(39) +'''''''
'''')
WITH RESULT SETS( 
(
            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),
            RecoveryModel nvarchar(60),
            DifferentialBaseLSN numeric(25, 0),
            DifferentialBaseGUID uniqueidentifier,
            BackupTypeDescription nvarchar(60),
            BackupSetGUID uniqueidentifier,
            CompressedBackupSize bigint,
            Containment tinyint,
            KeyAlgorithm nvarchar(32),
            EncryptorThumbprint varbinary(20),
            EncryptorType nvarchar(32)
))'') AS a'
print @Sql 
 exec (@sql)

and the result I get on my laptop is this:

This is how I took the backup for the result above:

--taking a backup with copy only and checking later the results on headeronly
backup database [AdventureWorks2019]
to disk='C:\backups\DIFF\my_server_AdventureWorks_diff.bak'
with copy_only,compression,no_checksum,stats=1,init,format

now with checksum and blocksize:

--taking a backup with copy only and checking later the results on headeronly - now with checksum and blocksize
backup database [AdventureWorks2019]
to disk='C:\backups\DIFF\my_server_AdventureWorks_diff.bak'
with copy_only,compression,checksum,stats=1,init,format,blocksize=4096

and the result for this one:

Extracting a field from RESTORE HEADERONLY

6 Answers6

-- marcello miorelli

Linked