79

I have a Sharepoint server. We had an issue with our backup tool and now some of my databases are stuck in restoring state!

Image showing restoring databases

Is it possible to stop the restoring process? and also, How can I make sure the database integrity has not been compromised?

128

This is likely caused by the restore script adding the WITH NORECOVERY parameter, to make the database ready for a transaction log apply after the restore.

The database is now waiting for the latest transaction log file.

You can either:

  1. Apply the latest transaction log, using RESTORE LOG database_name FROM backup_device WITH RECOVERY; ... or
  2. Restore the database again, but this time using ... WITH RECOVERY; ... or
  3. Force the database out of restoring mode by executing: RESTORE DATABASE YourDb WITH RECOVERY;

Before you do this, please make sure you understand the implications of these options. You may cause data loss if you are not careful.

See this for details:

  • When those don't work, see dba.stackexchange.com/questions/11175/… -- again, LOTS OF WARNINGS OF DOOM if you don't know what you're doing and understand the full risk of data loss. But if you're in a bind on a non-production server, you've got good backups to do a fresh restore from, and you can afford to restart SQL on said server, it works. – NateJ Feb 26 '16 at 1:18
  • 6
    "Force the database out of restoring mode by executing: RESTORE DATABASE YourDb WITH RECOVERY". 20 minutes of head scratching and restarting services, then fixed with a one liner. Thanks! – Echilon Feb 15 '17 at 9:14
  • 2
    I was stuck with the same situation my dev box. Option 3 worked like a charm! – Moiz Tankiwala Feb 17 '17 at 6:20
  • I tried to restore a DB from .bak into a new DB, but forgot to change file names on disk. Restoring failed, although not because the files were in use by an existing database, but some other error. This messed up the existing DB (stuck in restoring state). #3 put it back online in a second and checking the data everything looks fine. Thanks! – CoDEmanX May 10 '17 at 8:56
42

Simple T-SQL Script for Solving this problem :

write this script on New Query window and execute :

RESTORE DATABASE [DataBase Name] WITH RECOVERY;
  • RESTORE DATABASE [database_name] WITH RECOVERY .... Worked perfect for me. Thank you! – user53174 Nov 30 '14 at 20:54
  • 10
    Be very careful to understand the implications of that. If the DB is awaiting a log restore that could result in considerable data loss. – David Spillett Nov 30 '14 at 21:01
  • 2
    Fails to work because of error, "The database cannot be recovered because the log was not restored." – Ian Boyd Sep 22 '16 at 20:18
0

I just had this situation, and the cure was quite surprising:

ALTER DATABASE DBName SET ONLINE;

Apparently the NetBackup restore that broke left it in a weird state. No other solution worked (though I hadn't yet tried restarting the SQL Server service)

I'd be careful with the database, though, as theoretically once a restore starts, then fails, you could have corrupted data. I'm just going to restore the database again anyway, so it doesn't matter to me.

  • This is the wrong answer for this question, if you try to set a database that is in 'Restoring' Online you will get the error "ALTER DATABASE is not permitted while a database is in the Restoring state." – James Jenkins Jan 17 at 17:48
  • Perhaps you are unfamiliar with how on StackOverflow, we sometimes answer questions that are slightly different from the posted one. This provides value because people often come to questions similar to theirs after searching online. Regarding your factual claim about ALTER DATABASE not being permitted, I assure you that in my case, the database was in fact showing as in restoring state, and using SET ONLINE in fact worked. So realize that you are calling me mistaken or lying. Is that what you really need to do when an alternative is available? – ErikE Jan 20 at 7:21
  • @JamesJenkins And what is that alternative? The alternative is the hypothesis that a database could show in state restoring but not actually be restoring. Or it could be at the final step of restoring, with the restore actually completed so that it could actually be set to ONLINE state. So long story short, I think your comment and downvote are out of line for this site and your information is factually incorrect because it is not properly qualified with the data I have presented. – ErikE Jan 20 at 7:29
0

As we know, the default database restore option is with Recovery which ensures the database is available and Online for use after completion of database restore.

Example:

RESTORE DATABASE YourDB FROM DISK= 'C:\\Data\\YourDBBackup.bak'
WITH RECOVERY
GO

Let’s see the important points about Restore with NO Recovery

  • Database is not usable
  • Remains in restore mode
  • Next restore sequence can be done
  • Doesn’t rollback any uncommitted transactions

Restore With NoRecovery

This option especially used when multiple backup are to be restored. It means, when you execute the restore command with norecovery option that means, the database is not released to the users until last backup in sequence is restored. With last backup, the Recovery option is used and database goes online.

Example:

RESTORE DATABASE YourDB FROM DISK - 'C:\\Data\\Backup_part1.bak' 
WITH NORECOVERY
GO

And then:

RESTORE LOG YourDB FROM DISK = 'C:\\Data\\BackupLog-part2.trn'
WITH RECOVERY
GO
  • 1
    You might have a typo in your answer. The section marked ...about Restore with Recovery lists all options that apply to the RESTORE ...WITH NORECOVERY option. Could you double-check that? – hot2use Dec 10 '18 at 14:05

protected by Community Dec 18 '14 at 11:19

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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