July 2, 2010 at 7:32 am
This started with the rather unhelpful message 'RESTORE DATABASE is terminating abnormally' when I was trying to refresh a database on my development server. Don't know why, the same backup file worked OK last week. Anyway, the database is now marked as 'Restoring..' in object explorer. I deleted the database (tried with the GUI and with tSQL, same outcome) and
it disappears from object explorer. Doesn't show in the output of sp_helpdb either. However, execute a 'select * from sysdatabases' and it is still there and if you refresh the object explorer databases node in SSMS it comes back again - still shown as restoring - although still not in sp_helpdb output. You can't delete the row from sysdatabases (ad-hoc updates to system catalogs not allowed), so how do I actually get rid of it? Luckiliy this is only a development box, I dread to think that this situation may occur in a production environment.
Does anyone have any bright ideas on how I can resolve this? - Apart from reinstalling SQL Server!:w00t:
Tony
July 5, 2010 at 5:41 pm
That is a nasty one. If I were you I would stop the sql instance and move or rename the data and log files and start SQL again and see if that helps you in cleaning it up. If that doesn't work instead of doing a backup copy the data and log file from the server and paste them into your dev server and start SQL they should be mount and not be flagged as restoring.
July 6, 2010 at 12:28 am
u sure u havent done log shipping or mirroring??
July 6, 2010 at 12:28 am
u sure u havent done log shipping or mirroring??
July 8, 2010 at 5:11 am
I too have facedSimilar problem ,
I restored the database with another database backup which is too less in size(100Mb).Further i dropped the database it worked from SQL console as well as OS.
July 8, 2010 at 7:22 am
Guess what? I came back to this problem today after a few days off and the problem has gone away.
Logged on to SSMS this morning, tried the drop database command - and it went - completely - no record left in sys.databases at all! I was able to restore the same backup with no problems, and a subsequent dbcc checkdb reproted no problems. All I have done since the original post is to hibernate the workstation - not even a reboot.
I suppose I suppose I just put it down to experience - and hope it never happens agains!
Thanks everyone.
Tony
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply