Restore Issue?

• 
  Could you please run the following:
  
  EXEC master..sqbutility 9997
  
  which will then generate a file named SQBCoreService_bugreport.txt in the folder where the SQL Backup Agent (SQBCoreService.exe) is installed. Please send me that file.
  
  Thanks.

  petey April 23, 2008 12:04

  - 0 +
• 
  Thanks for sending the file.
  
  The log doesn't show that any active restore process running within SQL Backup. However, it also shows that the service has only been running for 33 minutes.
  
  Was the service restarted while the restore was running?

  petey April 23, 2008 12:33

  - 0 +
• 
  After more than 48 hours, we stopped the restore. We are trying to come up with another solution that may work. We were told that the restore process would be as faster, if not faster than the backup process. That obviously isn't the case.

  thomascroth April 23, 2008 12:36

  - 0 +
• 
  Given that you were creating a new database from the restore, SQL Server would have first preallocated 260 GB of space for the new database. That would have taken some time, depending on the speed of your drives.
  
  If you do attempt the restore again using SQL Backup, you can use
  
  EXEC master..sqbstatus
  
  to monitor the restore progress.

  petey April 23, 2008 12:56

  - 0 +
• 
  I have the same issue where it is locking up, but shows the restore already being completed. However, with my issue, I cannot get the restore to stop altogether. This is a very small restore I am working with (4MB).
  
  I closed management studio and stopped and started the SQL Backup Agent (twice). Red Gate and my server still show the database is being restored.
  
  Any thoughts on what I should do?

  jlucas April 29, 2008 15:46

  - 0 +
• 
  Could you please send me the backup file? Could you also please run
  

  EXEC master..sqbtutility 9997
  

  which generates a log file named SQBCoreService_bugreport.txt in the folder where the SQL Backup Agent service executable (SQBCoreService.exe) is installed, and send me that file?

  petey April 29, 2008 22:25

  - 0 +
• 
  Hi all,
  
  I'm collating all of these reports of 'hanging restore jobs' and I am having difficulty finding any commonality at all.
  
  jlucas -- you're saying that the database is in a restoring state, what is the indication? Is the word (restoring) next to the database name in SQL Server Manaagement Studio and SQL Backup? If so, you may have simply forgot to restore the database using the WITH RECOVERY option.
  
  Thomas -- are you running this restore job from SQL Backup, using the sqlbackup extended stored procedure in a query tool like Enterprise Manager, or as part of a scheduled SQL Agent job? What is the indication that the restore is taking place still, for instance that SQL Agent shows the job as running, or an sp_who2 query, or the sqbstatus extended stored procedure?
  
  Looking forward to your replies.

  Brian Donahue April 30, 2008 10:29

  - 0 +
• 

  Brian Donahue wrote:

  jlucas -- you're saying that the database is in a restoring state, what is the indication? Is the word (restoring) next to the database name in SQL Server Manaagement Studio and SQL Backup? If so, you may have simply forgot to restore the database using the WITH RECOVERY option.

  
  Yes, that is correct. What is quirky about this all is that Red Gate says the restore is complete. I also noticed that it did not allow me any way to break the restore process (I was trying just ot get into my database at this point). I did try to stop the SQL Backup Agent, but it didn't work.
  
  What I ended up doing was:
  - Wait a few hours
  - Verify it still showed restoring next to the db name in Red Gate and Management Studio
  - I then did a differential restore which did the restore once again, but opened my database immediately
  
  This is really all the information I have to go off of. Is there a better way to handle this in the future? I already plan on creating a secondary backup process.

  jlucas April 30, 2008 18:22

  - 0 +
• 
  Thanks for writing back.
  
  SQL Server is designed to support different levels of recovery. This means that you can complete a restore operation in a way that allows the server to accept additional restores afterwards. For instance, you can restore a full, then a differential, but if you want to do that you need to restore the full with NORECOVERY and in that circumstance, the status of the database is displayed as restoring in SSMS and SQL Backup.

  Brian Donahue May 01, 2008 04:11

  - 0 +
• 
  That is exactly what happened and then the restoring never went away and I was locked out from doing anything to the database. In the future, I will do restores with the Operational selection.
  
  Do you know if there are any other ways to stop the restore if it seems frozen? I understand it may only restore portions of the database if I do this. I stopped the SQL Backup Agent and restarted it, but that didn't stop it.

  jlucas May 01, 2008 09:41

  - 0 +
• 
  Hi,
  
  In your situation, the restore had completed successfully. The SQL Server is waiting for additional restores to be done so that the database can be brought online. If you specified WITH NORECOVERY by mistake and don't have additional backups to restore, then you can bring the database online by doing a 'fake' restore:
  
  RESTORE [MyDatabase] WITH RECOVERY
  
  This will put the database online.

  Brian Donahue May 01, 2008 09:52

  - 0 +
• 
  Perfect! Thanks for the help.

  jlucas May 01, 2008 10:00

  - 0 +

Add comment

Please sign in to leave a comment.