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
Best answer: Oracle
Hi I ran the following script on the secondary server RESTORE DATABASE NameofDatabase
WITH RECOVERY, the database is no longer in restoring state but now its not in Synchronized state also. when I check the same database on Primary server it is on Synchronized state. How do I make sure the database on the second server is also on Synchronized state.
Thank you. Your post wasn’t published last time I had this problem but it saved me a lot of time just now :)!
When trying the above code it states that it cannot complete as it is still in recovery mode. We have access to the log file but not sure what to do, can you assist?
The database cannot be recovered because the log was not restored.RESTORE DATABASE is terminating abnormally.
@Reshma
What was the restore sequence you followed?
I’m having the same issue as Reshma. I executed:
RESTORE DATABASE NameofDatabase
WITH RECOVERY
and got the log not restored error, so then I executed:
RESTORE DATABASE NameofDatabase
WITH RECOVERY, REPLACE
but still get this same issue. HELP!
Hi Pinal,
Basically I ran a backup of 2 DBs and restored as two different DBs (also changing the names of MDF and LDF files) on the same SQL Server.
The 2 new DBs are working fine, but the original DBs are stuck in Restoring state. As I still need the data from original DB what exactly should I do?
Thank you.
Vinicius
What was the command you run to backup the database? If you run backup with norecovery then database would go to restoring state.
I did the page restore on the existing database dbname,
RESTORE DATABASE dbname PAGE = ‘1:1025569,1:1025184’
FROM DISK = ‘D:\abc.bak’ WITH NORECOVERY
And tried to take the log back from database after done the page restore using
BACKUP LOG shikar_centralnew TO DISK=’D:\abc.trn.’; It is showing below error
Database ” cannot be opened. It is in the middle of a restore.
after that when i try to recovery the database by using
RESTORE DATABASE databasename with recovery. It is showing below error.
The roll forward start point is now at log sequence number (LSN) 594591000000063900001. Additional roll forward past LSN 600404000000044500001 is required to complete the restore sequence.
Now the sql server it not allowing to take the database to online in any way. Can you help me to resolve this issue.
Did you take log backup before restore? If yes, you need to restore that.
more complex probelm:
1. asked to restore a db to another instance under a different name, to include MDF and LDF as well as the DB name.
2. of course if you just restore the .BAK file you cannot do this easily from SQL so I used the SSMS to
a. create a blank db name of the desired new name
b. used SSMS to indicate the copied over .BAK file to the new database I have created in ‘a’ above.
c. attached, using SSMS, the .BAK to the correct MDF and LDF file as the .BAK will keep the original LDF/MDF and DB name in its header info: didn’t want to overwrite the existing database on this ‘new’ instance as they had a DEV db there to protect, thus the ‘new db name’ to create a new db for DEV.
d.. made sure to also ‘with recovery’
e. did with overwrite….check all attachments and hit ‘restore’
3. NOW it went south. notice BOTH the original db was being overwritten, apparently, can’t tell but saying it was being restored, as well as the new db I created. Panicked and hit STOP on the SSMS wiz interface.
4. BOTH dbs are in ‘restoring mode’ and stuck
5. don’t know how to cancel both dbs from ‘restoring’ and fearful that I may need to just write a lengthy query to do something simple OR can I just kill the agent, wait a bit and try my best.
not sure where to go with this.
r
Msg 5011, Level 14, State 7, Line 4
User does not have permission to alter database ‘Database name’, the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 4
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 7, Line 5
User does not have permission to alter database ‘database name’, the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 5
ALTER DATABASE statement failed.
This helped me..Thanks Dave..
Thanks for leaving a comment chinthanaronline. I am glad that it helped you.
I’m getting the following error while running the above command.
Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)
any help is much appreciated.
Thank you so much Pinal! This worked perfectly. You have saved my butt again.
Glad it worked Scott. Thanks for your note.
I am facing some hung issue during DB restore on LoadTest. Working on to fix and will let you know once done.
Here is what I have done:
I have checked all the logs (SQL Server and Windows) to look for any errors or any messages that looked suspicious. There is nothing.
I have tried dropping the databases\deleting the .mdf & .ldf files and recreating the databases and then restoring, still get the issue
I have made sure all connections where killed before starting the restore. Also, have placed the databases in single user mode before starting the restore. But Still same issue.
I have ran the restores using the GUI, writing out the script, and using custom scripts, same result
Please help on this.
I am facing some hung issue during DB restore on Test DB.
Here is what I have done:
I have checked all the logs (SQL Server and Windows) to look for any errors or any messages that looked suspicious. There is nothing.
I have tried dropping the databases\deleting the .mdf & .ldf files and recreating the databases and then restoring, still get the issue
I have made sure all connections where killed before starting the restore. Also, have placed the databases in single user mode before starting the restore. But Still same issue.
I have ran the restores using the GUI, writing out the script, and using custom scripts, same result
Please help on this.
best practices for me swiching from restore gui to querry window
and replace REPLACE using REPLACE,RECOVERY
This way the (Resoring…) state doesn’t go to stuck
so sollution worked for me thx
@Martin – I am glad that the blog was able to help you
Hi Pinal,
Following is the error :
The database cannot be recovered because the log was not restored.
Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.
can u help me out on this
What were the steps you have performed?
restore database is terminating abnormally
database stucks in restoring…
Getting error while taken full backup. Failed:(-1073548784) Executing the query “BACKUP DATABASE [model] TO DISK = N’F:\\Backup\\All…” failed with the following error: “Database ‘model’ cannot be opened. It is in the middle of a restore.
BACKUP DATABASE is terminating abnormally.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly. Pls advise