Take the 2-minute tour ×
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
add comment

1 Answer

up vote 4 down vote accepted

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''''
'')')
share|improve this answer
    
I dont think I will be able to sell accepting the security implications of turning on ad hoc queries:/ Still though, thank you for the idea. Other people might be able to use it. –  alun Feb 8 '12 at 11:05
1  
@alun - You can set up a linked server instead. –  Martin Smith Feb 8 '12 at 11:17
    
Interesting. I will give that a try. Thank you. –  alun Feb 8 '12 at 12:22
    
This seems to work perfectly. Nice work! –  alun Feb 8 '12 at 14:11
    
+1 Pretty clever way to avoid having to declare the table. –  Jason Cumberland Feb 8 '12 at 18:50
add comment

Your Answer

 
discard

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.