RESTORE SQBHEADERONLY returns multiple record sets
johnsongb
Posts: 12
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
'-sql "RESTORE SQBHEADERONLY FROM DISK = [file.sqb]"'
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).
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
'-sql "RESTORE SQBHEADERONLY FROM DISK = [file.sqb]"'
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).
Comments
Probably a little late for you now but RESTORE HEADERONLY works with SQL Backup as well.
master..sqlbackup '-SQL "RESTORE HEADERONLY ..."'
- James
Head of DBA Tools
Red Gate Software Ltd
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
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!
J