HELP - Restore is stuck
-
I am trying to restore a very small database. The restore has been running for over 20 hours and still is in the "RESTORING" state. The process seems to be hung up somewhere but see nothing in the logs. Does anyone know how to kill the process? Or anything I can do to get this database back up, even if it is to the previous version before the restore attempt?
Any help ASAP would be greatly appreciated!
-
If you are restoring through EM/QA cancell the restore or
run SP_WHO2 ACTIVE and get the SPID for the restore process then kill the SPID using KILL <SPID> command...
MohammedU
Microsoft SQL Server MVP -
Or once you run the "sp_who2" command, you might find that the SPID performing the restore is being blocked?
-
Thank you both!
-
I'm having the same issue but I am unable to connect to that sqlserver altogether so I cant get into the Query to run the command. I can get into another db on that same server though. Any other suggestions?
-
Try a DAC (dedicated administrative connection) !
That should always allow a single sysadmins connection to perform some very basic stuff.
Keep in mind remote DAC is off by default, so you may need to have a remote desktop kind of connection.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
i tried to do this but it tells me Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.
-
That seem logical to me ...
You simply cannot kill your own sessions actual connection spid.
Questions:
- please provide @@version information
- Did you refresh your object explorer pane ?
- sp_who2 can provide your the spid which is executing the restore command (or off course the query panel that is actually executing it shows it in the bottom right corner)
If you need to kill a spid, kill the spid that is executing the restore command.
You db will stay in a restoring state because a restore has been interrupted and you'll need to re-execute a valid restore statement.
Also keep in mind that if you run a restore command using the " with norecovery" parameter, the db will stay in a restoring state until you:
- finish to restore extra differential backups
- finish to restore extra log backups
and you finish one of the above statements using the " with recovery" parameter.
If you restored a full backup and as a precaution used the "with norecovery" parameter, but don't need to restore extra differential or log backups, you can just issue
restore database yourdb with recovery ;to get your db online and start the rest of your recovery check sequence and maintenance.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
hi i am having the same issue , i killed the spid but it remains as "restoring" is there anyway to change that without running another restore?
-
Even i faced the same problem, and i dropped the database name then restored again. And it worked for me.
USE MASTER
GO
DROP DATABASE <dbname>
--- then restore the database again.
-
If it's a huge database you can add the "restart" parameter to your (original) restore statement, and it will pick it up where it got killed.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I had the same problem, i fixed it with an easier way than you suggested, just renamed the restoring stuck database, restoring gone, put the same name again and run the restore. it worked in 20 seconds.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply