Options

Unable to restore backup sqb file

dbouchard@tricomputer.comdbouchard@tricomputer.com Posts: 14 Bronze 2
edited November 20, 2016 11:18PM in SQL Backup
Well, this is depressing. I have hundreds of sqb backup files and this is the first time I'm trying to restore one ever! when I do, I get the error shown in the attached image file. what can I do to restore the data?

In case the image doesn't come through, there is a red x in the OK column, Unknown in the date and type columns, Unavailable in the File name column, and pretty much Unknown in the rest of the columns.
· Share on Twitter

Comments

  • Options
    peteypetey Posts: 2,358 New member
    Could you please run the following commands in Management Studio, and post the results? Replace <file name> with the actual backup file name.
    EXEC master..sqlbackup '-sql "RESTORE VERIFYONLY FROM DISK = &#91;&lt;file name&gt;&#93;"'

EXEC master..sqlbackup '-sql "RESTORE SQBHEADERONLY FROM DISK = &#91;&lt;file name&gt;&#93;"'
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • Options
    dbouchard@tricomputer.comdbouchard@tricomputer.com Posts: 14 Bronze 2
    1ST TASK:

    Verifying file:
    \tcaraid8tbPublicSQLBackupsFULL_(local)_VCM_20150411_120013.sqb

    Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
    The path specified by "C:MSSQLMSSQL11.MSSQLSERVERMSSQLDATAVCM_data.mdf" is not in a valid directory.
    Directory lookup for the file "C:MSSQLMSSQL11.MSSQLSERVERMSSQLDATAVCM_log.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.

    2ND TASK:

    Reading SQB file header of "\tcaraid8tbPublicSQLBackupsFULL_(local)_VCM_20150411_120013.sqb"

    Backup group ID : 96258477
    File number : 1 of 1
    Threads : 7 (multi-threaded file)

    Backup type : 1 (Database)
    Native backup size : 11.063 MB
    Database size : 6.492 MB
    Backup start : Saturday, April 11, 2015 12:00:13 PM
    Backup end : Saturday, April 11, 2015 12:00:13 PM

    Server name : TRILAPTOP6
    Database name : VCM

    First LSN : 33468000000031800037
    Last LSN : 33468000000033500001
    Checkpoint LSN : 33468000000031800037
    Database backup LSN : 33462000000006100037
    · Share on Twitter
  • Options
    peteypetey Posts: 2,358 New member
    The output suggests that the backup file is valid. Could you please try restoring it via SSMS, though you might need to move the files around using the MOVE options e.g.

    EXEC master..sqlbackup '-sql "RESTORE FILELISTONLY FROM DISK = [\tcaraid8tbPublicSQLBackupsFULL_(local)_VCM_20150411_120013.sqb]"'

    to get the logical names of the data and log file, then

    EXEC master..sqlbackup '-sql "RESTORE DATABASE [VCM_sqbtest] FROM DISK = [\tcaraid8tbPublicSQLBackupsFULL_(local)_VCM_20150411_120013.sqb] WITH MOVE [<logical data file name>] TO [<new data file path>], MOVE [<logical log file name>] TO [<new log file path>]"'
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • Options
    dbouchard@tricomputer.comdbouchard@tricomputer.com Posts: 14 Bronze 2
    could you please let me know why I couldn't restore it using SQL Backup Pro directly? I've not yet been able to try your ssms restore, but certainly I don't expect to have to do this every time...
    · Share on Twitter
  • Options
    peteypetey Posts: 2,358 New member
    Try running the RESTORE FILELISTONLY command on the file, and let us know if the database details show up correctly:
    EXEC master..sqlbackup '-sql "RESTORE FILELISTONLY FROM DISK = &#91;\tcaraid8tbPublicSQLBackupsFULL_(local)_VCM_20150411_120013.sqb&#93;"'
    This is what the GUI is trying to do when you encountered the error.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • Options
    dbouchard@tricomputer.comdbouchard@tricomputer.com Posts: 14 Bronze 2
    It worked just fine, it gave me 2 rows of the data and log file with values that seem correct. do you need to see the values?
    · Share on Twitter
  • Options
    peteypetey Posts: 2,358 New member
    It's ok, I don't need to see the output.

    Could you please run Profiler to trace the last command that the SQL Backup GUI tries to run on the SQL Server instance before it reports the 'Unknown' errors?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • Options
    dbouchard@tricomputer.comdbouchard@tricomputer.com Posts: 14 Bronze 2
    declare @p3 nvarchar(4000)
    set @p3=N'0'
    exec master..sqbutility @Parameter1=1001,@Parameter2=N'L:SQLBackupsFULL_(local)_VCM_20150411_120013.sqb',@Parameter3=@p3 output
    select @p3
    · Share on Twitter
  • Options
    peteypetey Posts: 2,358 New member
    1001 is used to check if the SQL Backup Agent can find the 'L:SQLBackupsFULL_(local)_VCM_20150411_120013.sqb'. Using Profiler to trace the qeuries, could you please run the same command in Management Studio i.e.

    declare @p3 nvarchar(4000)
    set @p3=N'0'
    exec master..sqbutility @Parameter1=1001,@Parameter2=N'L:SQLBackupsFULL_(local)_VCM_20150411_120013.sqb',@Parameter3=@p3 output
    select @p3

    Does it return a result of 1? Are the NTUserName and LoginName values the same as it was when the GUI was running the same query?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.