Got an error when trying to use "Restore headeronly"

zynrgzynrg Posts: 16
edited December 10, 2007 1:20PM in SQL Backup Previous Versions
declare @sql varchar(max)
set @sql = '-SQL "Restore headeronly from disk = ''\\BackupServer\BackupA\DB_20071201_013002.sqb''"'
exec master.dbo.sqlbackup @Sql

exitcode 0
sqlerrorcode 0
filename001 \\BackupServer\BackupA\DB_20071201_013002.sqb

Comments

  • peteypetey Posts: 2,358 New member
    Could you pls try RESTORE VERIFYONLY, RESTORE SQBHEADERONLY and RESTORE FILELISTONLY on that file, and check if any output is provided?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Thanks Petey for your reply.

    I don't think it's a file-related issue because I have tried a couple of files.

    RESTORE FILELISTONLY works, but RESTORE VERIFYONLY and RESTORE SQBHEADERONLY don't work as well as HEADERONLY.

    BTW, currently, I'm using 5.2 version for SQL 2005.

    Here are some results:

    --===============================================================--
    declare @sql varchar(max)
    set @sql = '-SQL "Restore verifyonly from disk = ''E:\Backup_A\FULL_(local)_master_20071203_234212.sqb''"'
    exec master.dbo.sqlbackup @Sql

    SQL Backup v5.2.0.2807
    Verifying file:
    E:\Backup_A\FULL_(local)_master_20071203_234212.sqb

    Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
    Directory lookup for the file "D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf" failed with the operating system error 3(The system cannot find the path specified.).
    Directory lookup for the file "D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf" failed with the operating system error 3(The system cannot find the path specified.).
    The backup set on file 1 is valid.
    SQL Backup process ended.



    (10 row(s) affected)

    name value

    exitcode 0
    sqlerrorcode 0
    filename001 E:\Backup_A\FULL_(local)_master_20071203_234212.sqb

    (3 row(s) affected)
    --===============================================================--

    --===============================================================--
    declare @sql varchar(max)
    set @sql = '-SQL "Restore headeronly from disk = ''E:\Backup_A\FULL_(local)_master_20071203_234212.sqb''"'
    exec master.dbo.sqlbackup @Sql

    SQL Backup v5.2.0.2807
    Reading file header of "E:\Backup_A\FULL_(local)_master_20071203_234212.sqb"

    Backup name : Database (master), 12/3/2007 11:42:12 PM
    Description : Backup on 12/3/2007 11:42:12 PM Server: CTESDTSS Database: master
    Backup type : 1 (Database)
    User name : CTESDTSS\osun
    Backup start : 12/3/2007 11:42:13 PM
    Backup finish: 12/3/2007 11:42:13 PM

    Server name : CTESDTSS
    Database name : master
    Database version : 611
    Creation date : 11/14/2007 6:24:11 PM
    Size : 3.313 MB
    Sort order : 52
    Code page : 0
    Machine name : CTESDTSS
    Collation : SQL_Latin1_General_CP1_CI_AS

    First LSN : 432000000003200037
    Last LSN : 432000000005600001
    Checkpoint LSN : 432000000003200037
    Database backup LSN : 431000000019200037
    Differential base LSN : NULL

    SQL Backup process ended.



    (28 row(s) affected)

    name value

    exitcode 0
    sqlerrorcode 0
    filename001 E:\Backup_A\FULL_(local)_master_20071203_234212.sqb

    (3 row(s) affected)
    --===============================================================--

    --===============================================================--
    declare @sql varchar(max)
    set @sql = '-SQL "Restore sqbheaderonly from disk = ''E:\Backup_A\FULL_(local)_master_20071203_234212.sqb''"'
    exec master.dbo.sqlbackup @Sql

    SQL Backup v5.2.0.2807
    Reading SQB file header of "E:\Backup_A\FULL_(local)_master_20071203_234212.sqb"

    Backup group ID : 100457965
    File number : 1 of 1

    Backup type : 1 (Database)
    Native backup size : 3.688 MB
    Database size : 5.000 MB
    Backup start : Monday, December 03, 2007 11:42:13 PM
    Backup end : Monday, December 03, 2007 11:42:13 PM

    Server name : CTESDTSS
    Database name : master

    First LSN : 432000000003200037
    Last LSN : 432000000005600001
    Checkpoint LSN : 432000000003200037
    Database backup LSN : 431000000019200037



    (20 row(s) affected)

    name value

    exitcode 0
    sqlerrorcode 0
    filename001 E:\Backup_A\FULL_(local)_master_20071203_234212.sqb

    (3 row(s) affected)
    --===============================================================--
  • peteypetey Posts: 2,358 New member
    Could you pls download this utility, select the 'Check file integrity' function, and run the check on the file that you are having problems with (FULL_(local)_master_20071203_234212.sqb'')? Does it report any problems?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Hey Petey,

    Here is the resutl:

    12/5/2007 11:32:22 AM Not encrypted
    12/5/2007 11:32:22 AM Single device file
    12/5/2007 11:32:22 AM Compression level: 1
    12/5/2007 11:32:22 AM File size: 664,576
    12/5/2007 11:32:23 AM File read stopped at position: 664,233
    12/5/2007 11:32:23 AM Remaining: 343
    12/5/2007 11:32:23 AM Validating remaining bytes...
    12/5/2007 11:32:23 AM File appears to be valid
    12/5/2007 11:32:23 AM File read ended at position: 664,576

    Did you try HEADERONLY on your computers? Could you successfully run it?

    Thanks,
  • Hey Petey,

    You may download this file and try it.

    http://pickup.mofile.com/6330744401386462

    declare @sql varchar(max)
    set @sql = '-SQL "Restore headeronly from disk = ''E:\Backup_A\FULL_(local)_model_20071203_234217.sqb''"'
    exec master.dbo.sqlbackup @Sql
    [/url]
  • peteypetey Posts: 2,358 New member
    This is the result I received, as designed:

    SQL Backup v5.2.0.2825
    Reading file header of "e:\temp\headerproblem.sqb"

    Backup name : Database (model), 12/3/2007 11:42:17 PM
    Description : Backup on 12/3/2007 11:42:17 PM Server: ABSRAIDT Database: model
    Backup type : 1 (Database)
    User name : ABSOLUTE\tsun
    Backup start : 12/3/2007 11:42:18 PM
    Backup finish: 12/3/2007 11:42:18 PM

    Server name : ABSRAIDT
    Database name : model
    Database version : 611
    Creation date : 4/8/2003 9:13:36 AM
    Size : 2.313 MB
    Sort order : 52
    Code page : 0
    Machine name : ABSRAIDT
    Collation : SQL_Latin1_General_CP1_CI_AS

    First LSN : 22000000040000037
    Last LSN : 22000000042400001
    Checkpoint LSN : 22000000040000037
    Database backup LSN : 22000000033600037
    Differential base LSN : NULL

    SQL Backup process ended.

    (28 row(s) affected)


    name value

    exitcode 0
    sqlerrorcode 0
    filename001 e:\temp\headerproblem.sqb

    (3 row(s) affected)


    Is this not what you get, or were you expecting something different? Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Yes, exactly, but it's entirely different from the sqlserver's result format.

    If you use FILELISTONLY, you can get the same result as sqlserver's.

    Actually, I'm using "Insert into @TempTable Exec(@Sql)", but if the result returns two tables with different column amount, I could not get the info. So far, I cannot figure out how to get these info in my stored procedure.

    Why you design it like this? Or give me some suggestions.

    Thanks,
  • peteypetey Posts: 2,358 New member
    Why you design it like this?
    Because it is easier to read. Also, I am not aware of any user requesting for the output to be consistent with that returned by SQL Server (until now, that is).
    Or give me some suggestions.
    Use the SINGLRESULTSET option to output only the first result set. E.g.

    CREATE TABLE #temp1 (col1 nvarchar(256))
    INSERT INTO #temp1 EXEC master..sqlbackup '-sql "RESTORE HEADERONLY FROM DISK = [e:\temp\pubs.sqb] WITH SINGLERESULTSET"'
    SELECT * FROM #temp1
    DROP TABLE #temp1
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Hey Petey,

    That's exactly what I want, "WITH SINGLERESULTSET". Thank you a lot for your quick reponses.

    BTW, probably, I'm the first one who want to use this funcationality in programming. :)

    It's easy for human beings to read but hard for machines, even I use "WITH SINGLERESULTSET", it still need some further process to deal with the info. Anyway, it will be great, if you could add a parameter to let people specify the result format. That's only my personal suggestion. :)

    Thanks again.

    Neo
Sign In or Register to comment.