Still "Restoring..." 5 days later
-
I was trying to move a SS2000 database to a SS2005 server. I backed up the SS2000 database, copied the .bak file to the SS2005 box, and started to restore using the SSMS GUI. I realized there was not going to be enough disk space, so I clicked the "Cancel" button. That was last Friday. It is now Wednesday, and it still says, "Restoring..." next to the database in the GUI on the SS2005 box. No, the server is not that slow, and no, the original database was not that big. So now what?
There is no "i" in team, but idiot has two. -
Dave (6/24/2009)
I was trying to move a SS2000 database to a SS2005 server. I backed up the SS2000 database, copied the .bak file to the SS2005 box, and started to restore using the SSMS GUI. I realized there was not going to be enough disk space, so I clicked the "Cancel" button. That was last Friday. It is now Wednesday, and it still says, "Restoring..." next to the database in the GUI on the SS2005 box. No, the server is not that slow, and no, the original database was not that big. So now what?That's extreemly long indeed.
Can you try stop / start the sqlserver instance ?
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
-
That's extreemly long indeed.
Can you try stop / start the sqlserver instance ?
I think this is a 2005 bug because I have seen more of these strange behaviors so I think you may need to restart the SQL Server service.
Kind regards,
Gift Peddie -
ALZDBA (6/24/2009)
Can you try stop / start the sqlserver instance ?Yes, I have a maintenance window this evening.
There is no "i" in team, but idiot has two. -
I have seen instances where the application (in this case, Enterprise Manager) appears to be frozen, especially after cancelling an operation. However, it's not; it's just that the screens didn't get updated. Have you tried just closing down EM? Or does this show as "Restoring" on other EMs connecting to this server?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Before you do that - have you refreshed the information in Object Explorer? Or, just closed SSMS and re-opened it?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Yes, I have closed mgmt studio from my laptop, and gone home for the day, but it still says it's restoring when I bring it back up the next a.m. I just now tried EM from the server desktop, and it thinks the database is restoring as well. I am a little concerned about just bouncing the SS service, but it's tying up around 70 gigs of disk space for no good reason, and I don't know what else to try.
There is no "i" in team, but idiot has two. -
Have you tried deleting the restore? I did that when it happened to me but I did not wait for more than one hour.
Kind regards,
Gift Peddie -
Okay, since this was a cancelled restore you should be able to right-click the restoring database and delete it. Once deleted, you can then delete the mdf/ldf files if they still exist.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Yep, that worked. Thanks, all.
There is no "i" in team, but idiot has two. -
You are welcome - glad I could help.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Jeffrey Williams (6/24/2009)
Okay, since this was a cancelled restore you should be able to right-click the restoring database and delete it. Once deleted, you can then delete the mdf/ldf files if they still exist.Off course, what have I been focussing on.
I probably got confused with canceling a long running (update) query that may cause that connection to stay in a rollback state for a very long time. In many cases, this can only be speed up by restarting the sqlserver instance .
If you interupt a restore, the db will stay in a restoring state and you'll have to either :
- perform a new restore database (with replace!)
- drop the db yourself !
I'm sorry for this useless twist of mind. :blush:
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
-
Cancelling a restore is not the only thing that puts the Db is restoring mode and so it is not relevant, I deleted mine in the restoring mode without canceling the restore and created a new restore without issues.
Kind regards,
Gift Peddie -
Gift Peddie (6/26/2009)
Cancelling a restore is not the only thing that puts the Db is restoring mode and so it is not relevant, ...In this case it is relevant !
A restore operation that is canceled leaves your db in restoring state.
OP stated he canceled his restore operation, hence the db status.
You are correct that a db in "restoring" state may be caused by many other things, but in this case it is obvious what was the cause !
If the OP didn't mention this cancel of the restore command, many other scenarios would be offered to diagnose the problem, the cause of the problem and the fix (if possible).
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 was trying to move a SS2000 database to a SS2005 server. I backed up the SS2000 database, copied the .bak file to the SS2005 box, and started to restore using the SSMS GUI. I realized there was not going to be enough disk space, so I clicked the "Cancel" button. That was last Friday. It is now Wednesday, and it still says, "Restoring..." next to the database in the GUI on the SS2005 box. No, the server is not that slow, and no, the original database was not that big. So now what?
The above tells me even without the cancel without enough space that DB would still be in restoring mode so the cancel just created the restoring state that the lack of space would have created. So a delete is the quick solution I know for restoring state.
Kind regards,
Gift Peddie
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply