Database Stuck in Restore Mode

Question

Post reply

Database Stuck in Restore Mode

lklein

Ten Centuries

Points: 1234
More actions
December 3, 2007 at 11:04 am

#119591

I have a database that appears to be stuck in a restore mode. I've looked at the activity and job monitor and that database doesn't appear. How do I stop or cancel this?
Please...

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 1

Hope ur database is in restoring state....Try Restoring the database with recovery option....

allen davidson SSCertifiable Points: 7042 More actions · Answer 2

allen davidson

SSCertifiable

Points: 7042

December 3, 2007 at 11:22 am

#756598

Try

RESTORE DATABASE MyDB WITH RECOVERY

Allen

DBAsql-1046554 Ten Centuries Points: 1088 More actions · Answer 3

I am also getting the same problem. My database got stuck in restoring mode. The above mentioned command also gives out error.

"The database cannot be recovered because the log was not restored.

RESTORE DATABASE is terminating abnormally."

Can anyone provide me a quick suggestion on bring back the database.

Thank You in advance.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Drop the database and restore it from backup again. Either the restore failed part way through or the backup file used was damaged.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

itguy2015 Mr or Mrs. 500 Points: 583 More actions · Answer 5

I have a similar situation. I had a database "mydb" on a sql server instance on vm1. I also had mydb on a sql server instance on vm2.

I wanted to restore a backup of mydb from vm1 to vm2. However, I intended to restore to mydb_copy on vm2, but I accidentally started the restore to mydb on vm2, which would overwrite mydb on vm2, which is not what I wanted. So I cancelled the restore, and now it is stuck in the "restoring" mode for about 5 hours. It's a 6 gig database.

If I have to, it's OK if I end up totally losing mydb on vm2, but I prefer not to. I prefer to have the cancel run to completion and have mydb on vm2 remain as it was before I started the restore.

Does anyone have a recommendation on how to proceed?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

itguy2015 (12/22/2015)
I have a similar situation. I had a database "mydb" on a sql server instance on vm1. I also had mydb on a sql server instance on vm2.
I wanted to restore a backup of mydb from vm1 to vm2. However, I intended to restore to mydb_copy on vm2, but I accidentally started the restore to mydb on vm2, which would overwrite mydb on vm2, which is not what I wanted. So I cancelled the restore, and now it is stuck in the "restoring" mode for about 5 hours. It's a 6 gig database.
If I have to, it's OK if I end up totally losing mydb on vm2, but I prefer not to. I prefer to have the cancel run to completion and have mydb on vm2 remain as it was before I started the restore.
Does anyone have a recommendation on how to proceed?

Do a restore from the backups that you make for mydb on vm2. To the best of my knowledge, restores can't be rolled back even if they don't complete.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

itguy2015 (12/22/2015)
I prefer to have the cancel run to completion and have mydb on vm2 remain as it was before I started the restore.

You could leave it from now until eternity, nothing would change. It's not rolling back. Restores, because they overwrite the database file, cannot be rolled back. You need to drop the DB and restore the DB you accidentally overwrote from one of its backups, and then do the restore you intended.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

itguy2015 Mr or Mrs. 500 Points: 583 More actions · Answer 8

GilaMonster (12/23/2015)
itguy2015 (12/22/2015)
I prefer to have the cancel run to completion and have mydb on vm2 remain as it was before I started the restore.
You could leave it from now until eternity, nothing would change. It's not rolling back. Restores, because they overwrite the database file, cannot be rolled back. You need to drop the DB and restore the DB you accidentally overwrote from one of its backups, and then do the restore you intended.

Thanks GilaMonster and Jeff Moden! I followed your recommendation and now I am good!