How to extract the database name from a backup file

matt.stanford

I'm trying to figure out how to retrieve the database name from the header of a backup using TSQL. The issue I'm running into is that the sqlbackup procedure returns two datasets.

For example, with a Native SQL backup I can do:

SET @SQL = 'RESTORE HEADERONLY FROM DISK = ''' + @backupFile + ''''

insert into #header EXEC (@SQL)
-- Query #header to my heart's content

This of course fails when I try to something similar with master..sqlbackup because it has two different datasets that come back: one with the header information and the other with the success/failure information.

Can anyone give me some direction on how to handle this output or have an alternate way to retrieve the database name?

Thanks,
-Matt

Jason Cook

The first line would become (hopefully I've put enough quotes in, need to escape the escaped quotes!):

SET @SQL = 'master..sqlbackup ''-SQL "RESTORE HEADERONLY FROM DISK = ''''' + @backupFile + ''''' WITH SINGLERESULTSET"'';'

This will return the first result set (the HEADERONLY contents) but discard the second result set (the success status).

Hope that helps,
Jason

matt.stanford

Fantastic! That worked like a charm.

Thank you,
-Matt