SQL server databases stuck in restoring state
This is likely caused by the restore script adding the
WITH NORECOVERYparameter, 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:
- Apply the latest transaction log, using
RESTORE LOG database_name FROM backup_device WITH RECOVERY;... or
- Restore the database again, but this time using
... WITH RECOVERY;... or
- 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 http://dba.stackexchange.com/questions/11175/how-do-you-stop-take-offline-and-delete-a-sql-server-database-in-recovery -- 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.
"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!
I was stuck with the same situation my dev box. Option 3 worked like a charm!
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!
- Apply the latest transaction log, using
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!
Be very careful to understand the implications of that. If the DB is awaiting a log restore that could result in considerable data loss.
Fails to work because of error, *"The database cannot be recovered because the log was not restored."*
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."
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?
@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.
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.
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.
RESTORE DATABASE YourDB FROM DISK - 'C:\\Data\\Backup_part1.bak' WITH NORECOVERY GO
RESTORE LOG YourDB FROM DISK = 'C:\\Data\\BackupLog-part2.trn' WITH RECOVERY GO