Error in using sp_describe_first_result_set with Restore

Question

Is querying the backup metadata with sp_describe_first_result_set system sp supported?

For example the query:

SELECT * FROM sys.dm_exec_describe_first_result_set (N'RESTORE HEADERONLY FROM DISK =''C:\SQLBackups\SomeDBbackup.bak''',NULL,0)

returns NULL for each field.

error_message field:

The metadata could not be determined because statement RESTORE HEADERONLY .... does not support metadata discovery.

error_type_desc field:

UNSUPPORTED_STATEMENT

Is there a way to make this query work?

Thank you

sepupic · Accepted Answer · 2017-11-15T11:59:55.423

The metadata could not be determined because statement RESTORE HEADERONLY .... does not support metadata discovery.

Error message tells you clearly that RESTORE HEADERONLY is unsupported statement, it does not support metadata discovery.

So there is NO WAY to make this work. It is not implemented.

UPDATE

There is a solution in the link you posted, but the question there was NOT how to make work sp_describe_first_result_set with smth that is not supported (that is YOUR question), but how to get back only some columns from RESTORE HEADERONLY resultset, i.e. how to get the result of RESTORE HEADERONLY in "queryable" form.

There is a solution like this:

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

But as you see the Server in question was 2008.

Starting with SQL Server 2012 sp_describe_first_result_set was introduced, and now if you try to execute the query above, another thing is executed (you can see it using Profiler):

exec [sys].sp_describe_first_result_set N'SET FMTONLY OFF;
               EXEC(''
               RESTORE HEADERONLY 
               FROM DISK = ''''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\Backup\DB1.bak''''
'')',NULL,1

It's wrong to think that OPENQUERY/OPENROWSET always use sp_describe_first_result_set, you can prove it to yourself executing simple SELECT queries and monitoring execution using Profiler (even on 2012). But in your particular case the query is transformed as described above and you cannot do nothing with it.

Error in using sp_describe_first_result_set with Restore

1 Answers1

Linked