What are the challenges you face when working across database platforms? Take the survey

RESTORE SQBHEADERONLY returns multiple record sets

johnsongbjohnsongb Posts: 12
edited June 14, 2007 7:12PM in SQL Backup Previous Versions
This is going to be kind of a long explanation. This is for anyone that might have used the tsql command "RESTORE HeaderONLY" with some automated scripting. The "RESTORE SQBHEADERONLY" does not return results the same way.

To make a long story short, I ship files from several locations around the world to a central site. I restore the files to a server which I use for a central report database. Basically, my own version of log shipping. I don't use log shipping because the logs are actually extremely large compared to the full backup. So I ship the full backup and restore it. I bought SQL Backup because it compresses the files very nicely. One problem, the "RESTORE SQBHEADERONLY" command has different output then the SQL Server command "RESTORE HeaderONLY". With the SQL command, I could exec the "RESTORE HeaderONLY" for all the shipped files and insert the results into a table then analyze the info.

Something like this:

Insert into tbFileInfo Restore headeronly from disk = 'file'

Based on the file headers, I could restore the correct files and overwrite the correct databases. The SQLBackup command does not work the same way.

exec master..sqlbackup

This command returns two recordsets. This means I cant use straight tsql to insert the results into a sql table. At least, there is no way that I am aware of.

Anyway, the point of all this is that you can use a SSIS script task to read each record set from the "RESTORE SQBHEADERONLY" command into a sql temp table if neccessary. At least now, I have the file header info in a sql table. The format is not as nice as the sql command but at least now I can parse the output to get all the info I need (First LSN, Last LSN, Database Name, etc).


  • Options

    Probably a little late for you now but RESTORE HEADERONLY works with SQL Backup as well.

    master..sqlbackup '-SQL "RESTORE HEADERONLY ..."'

    - James
    James Moore
    Head of DBA Tools
    Red Gate Software Ltd
  • Options
    That command does the same thing:

    exec master..sqlbackup '-sql "RESTORE HEADERONLY FROM DISK = [file.sqb]"'

    It still returns two record sets vs the tsql command which returns one table with one row.

    SQL Backup v5.0.0.2770
    Reading file header of "\\amedwsapgr04\BITS\OPMPAC\OPMReporting\FULL_SQL2005_OPMReporting_20070501_221757.sqb"

    Backup name : Database (OPMReporting), 5/1/2007 10:17:57 PM

    name value
    exitcode 0
    sqlerrorcode 0
    filename001 \\amedwsapgr04\BITS\OPMPAC\OPMReporting\FULL_SQL2005_OPMReporting_20070501_221757.sqb
  • Options
    I am facing the same problem: trying to capture the output from RESTORE SQBHEADERONLY and process it automatically.
    Could you please post the script you are using to read multiple recordset to find first - last LSN from a backup file.
    Thank you!

Sign In or Register to comment.