How can we help you today? How can we help you today?

How to extract the database name from a backup file

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
matt.stanford
0

Comments

2 comments

Sort by
  • 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
    Jason Cook
    0
  • matt.stanford
    Fantastic! That worked like a charm.

    Thank you,
    -Matt
    matt.stanford
    0

Add comment

Please sign in to leave a comment.