Unable to restore backup sqb file

• 
  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 = [<file name>]"'
  
  EXEC master..sqlbackup '-sql "RESTORE SQBHEADERONLY FROM DISK = [<file name>]"'
  

  petey November 13, 2016 22:38

  - 0 +
• 
  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

  dbouchard November 16, 2016 20:36

  - 0 +
• 
  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>]"'

  petey November 16, 2016 22:13

  - 0 +
• 
  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...

  dbouchard November 17, 2016 11:31

  - 0 +
• 
  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 = [\tcaraid8tbPublicSQLBackupsFULL_(local)_VCM_20150411_120013.sqb]"'
  

  This is what the GUI is trying to do when you encountered the error.

  petey November 17, 2016 19:24

  - 0 +
• 
  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?

  dbouchard November 18, 2016 08:17

  - 0 +
• 
  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?

  petey November 18, 2016 08:47

  - 0 +
• 
  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

  dbouchard November 20, 2016 11:39

  - 0 +
• 
  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?

  petey November 20, 2016 23:18

  - 0 +

Add comment

Please sign in to leave a comment.