Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 temptable 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 temptable 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?

share|improve this question

2 Answers 2

up vote 5 down vote accepted

This works for me.

SELECT BackupStartDate 
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 
               'SET FMTONLY OFF;
               RESTORE HEADERONLY 
               FROM DISK = ''''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\Backup\DB1.bak''''
share|improve this answer
Clever, and thanks for sharing, but just for the record I think this is just as brittle/complex as the big-ole-list of columns in the end. Shame there's not an elegant solution. –  Tim Abell Oct 15 '14 at 19:58
@TimAbell - Yes I don't think I would actually use this in practice except maybe to get the table definition in the first place. –  Martin Smith Oct 15 '14 at 20:01

The old-fashioned way, for reference:

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

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;
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.