99

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?

Improve this question

4 Answers 4

Reset to default
147

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:

Improve this answer
4
  • 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, 2016 at 1:18
  • 7
    "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, 2017 at 9:14
  • 2
    I was stuck with the same situation my dev box. Option 3 worked like a charm!
    – Moiz Tankiwala
    Feb 17, 2017 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, 2017 at 8:56
58

Simple T-SQL Script for Solving this problem :

write this script on New Query window and execute :

RESTORE DATABASE [DataBase Name] WITH RECOVERY;
Improve this answer
5
  • RESTORE DATABASE [database_name] WITH RECOVERY .... Worked perfect for me. Thank you!
    – user53174
    Nov 30, 2014 at 20:54
  • 11
    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, 2014 at 21:01
  • 4
    Fails to work because of error, "The database cannot be recovered because the log was not restored."
    – Ian Boyd
    Sep 22, 2016 at 20:18
  • Perfect solution, thanks!
    – Hossein Amini
    Nov 23, 2019 at 19:39
  • worked perfect.
    – eMRe
    May 19, 2022 at 12:42
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.

Improve this answer
3
  • 1
    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, 2019 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, 2019 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, 2019 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
Improve this answer
0

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