1

We are going to move some 100+ databases and some of them are 1 TB each. To save time on Friday, we are going to perform restore from sunday's Full backups using with norecovery. And we do this during the week. And Diff restore with recovery on Friday to catch up.

Now I noticed during the week that if a full restore with norecovery fails, it still says "Restoring" ??. One would expect that the database would disappear.

So the question is. How can I double check on thursday let's say, that all databases that are restored from full backups with norecovery, actually are successfully restored so far, when both failed (but initiated successfully) and non failed restores, seems to both show "restoring" state?

Regards

1

The easiest would be simply to capture and review the output of the restore scripts, that provide the full detail of each restore, including any that encountered problems. How exactly to capture this will depend on where you are performing the restore - you can send the output from a SQL agent job step to a text file, for example.

If you haven't done that, I believe the SQL errorlog will capture the status of each restore as they finish.

  • Yes, I should've saved them, and I've also tried to output to textfile. But from query it only seems to write to text file after all backups are finished, so if SSMS crashes during restore, the text file wont be written I suppose. However, you are correct, it shows in error log. But, it doesn't show as failed. It only doesn't show the completion step. So 2 lines for failed (but no failure, error message) and 4 lines for success. My mistake is that I save some of the outputs but not all of them. – stack_henk Jun 27 '18 at 18:34
  • I can't edit my answer, but thanks :) I looked for the "Database was restored" line in error log, and deducted that all seems ok. – stack_henk Jun 27 '18 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.