Here is a one of the very common question I keep on getting via email.
“I just restored all the backup files of my database, however, my database is still stuck in restoring state. How do I fix it?”
Well, if you have already restored all the database, you can execute the following code and it will bring your database from recovery to operational state.
RESTORE DATABASE NameofDatabase
WITH RECOVERY
If due to any reason, above query returns error about restoring log files or any other file and you do not have that file in your hand, you can run following command.
Remember above command will roll forward your database and you will be not able to restore any other database after that.
RESTORE DATABASE NameofDatabase
WITH RECOVERY,REPLACE
If you are not sure what to do, leave a comment and I will help you out with your situation.
Reference: Pinal Dave (https://blog.sqlauthority.com)
50 Comments. Leave new
Please restore the database and log file, once again into the same database.
Thanks…Resolved my query….Db was stuck in restoring state.
Hello Pinal,
I got this error when I used restore with recovery
Msg 4333, Level 16, State 1, Line 1
The database cannot be recovered because the log was not restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
What should I have to do here?
@Mounika – how database came to this state?
Hello, I was restoring a play backup on a dev system using the UI and it just hung indefinitely. I tried the commands but it says it’s in use. Should I delete? I’m not sure it will even let me do that..
Thanks!
Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
yeah, better delete and restore.
Hello,
I am performing mirroring with the evaluation edition of MS SQL Server 2012 R2 edition.
I took the full backup of the database and a transactional log backup in the principle server.
I restored the full database backup in the mirror server , a message was displayed that resoration is successfully completed but on the object explorer, next to the database i still see the status as “Restoring”
Please note i have selected the mode as NO RECOVERY at the time of restoration as i am performing mirroring & i still have not restored the transactional log backup in the mirror server yet.
One DB is in bangalore site(local machine) & the other is in Nice site(VM). The size of the database is around 4821MB (These are just the info for reference)
Kindly suggest what needs to be done.
Thanks & Regards,
Sindhu
Sindhu – that’s how mirroring works. You need to initialize secondary by restore with norecovery option and database would be shown in restoring mode. Its normal.
Are you able to add this as mirror? What’s the exact issue?
I have restored one database(full backup in no recovery mode).
started mirroring from the principle server. When i choose the option “Start Mirroring” , i get the below error
Error :
Alter failed for database . (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The remote copy of database has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server , Error: 1412)
And i also tried mirroring by applying the transactional log backup after getting this error but no luck.
Request your suggestion on this.
Thanks & Regards,
Sindhu
Thanks a lot Pinal.
I recovered all my databases using simple query given in the article.
RESTORE DATABASE WMF_SAMS_IR1
WITH RECOVERY
Can you explain what are the possible causes for databases stuck in Restoring…. state?
Hi, actually our test server get restarted due to internal power issues, after that a particular database is in restoring mode . We tried to take a copy of mdf file and to attach with new DB. But nothing works. So I need your help . Guide me how to recover the database now.
I just had a colleague cause one of these inadvertently by accidentally trying to restore a backup over a replicating database. I don’t know how it got into the restoring state though as from personal experience in dev environments, SQL just won’t let you restore over a DB that is even marked for publication, let alone with a live publication. At any rate, it didn’t let the restore occur, but the db was then flagged as restoring.
Hi Pinal
I by mistake restored another database with same name but different structure. Now the database is in restore state. I want to undo the restore and bring back to original database before restore. Please help.
I restored with norecovery to start mirroring the database, and now it is stuck in restoring state, and I cannot mirror if it is in recovery mode
thoughts?
You saved my day sir, thank you so much!
hi I am getting the below meg and unable to restore the DB
Msg 4333, Level 16, State 1, Line 1
The database cannot be recovered because the log was not restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
please help on priority
Hi Sharmila,
Questions like this are difficult to answer on the blog. It would be best to hire a consultant for such issues as there are many variables.
Hi Pinal,
ALTER DATABASE is not permitted while a database is in the Restoring state getting frequently and also database is in freeze state.
Trying to setup mirroring. When I restore the backup in mirror server with norecovery leaves in recovering state and does not allow to set mirroring…so shows as not set for mirroring. How can ‘alter’ command be secuted to set mirroring when not allowed in restoring state??
Nice one. Keep giving such solutions.
I have about 50 customer databases on my SQL 2008 R2 server. Suddenly, without warning a DB went into “Restoring” state. ” RESTORE DATABASE NameofDatabase WITH RECOVERY” did not work. No errors. This DB takes Transaction Log every 15 minutes. The *.trn files are all intact. How do I script restore using the last .trn file?