After cancelling SQL job database stuck in restoring state

Question

After cancelling SQL job database stuck in restoring state

SQlLogic

SSCrazy Eights

Points: 9686
More actions
December 14, 2011 at 1:24 pm

#261606

Hi
I was doing restore operation and i cancelled SQL agent job in middle now my DB is stuck in Restoring State. can you suggest me what to do please?
Thanks

Viewing 15 posts - 1 through 14 (of 14 total)

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

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

Drop the database and start the restore from scratch.

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

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 2

SQlLogic

SSCrazy Eights

Points: 9686

December 14, 2011 at 1:31 pm

#1421585

Is there any other way rather than to DROP?

salum SSCommitted Points: 1598 More actions · Answer 3

logicinside22 (12/14/2011)
Is there any other way rather than to DROP?

As you canceled the restoration process in between which resulted database went inconsistency state so I will advice you rerun the job again and let the restoration complete.

java[/url]

george sibbald SSC Guru Points: 104210 More actions · Answer 4

you need to start another restore from the beginning, thats your only option.

you don't actually need to drop the database first

---------------------------------------------------------------------

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 5

george sibbald (12/14/2011)
you don't actually need to drop the database first

George, will it not fail as the physical database files exist and the rerun job cannot overwrite those files..

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

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

logicinside22 (12/14/2011)
Is there any other way rather than to DROP?

You can try RESTORE WITH RESTART, but unless the restore time is horrendous, probably unnecessary. The option's intended for absolutely huge databases.

http://www.sqlmag.com/article/database-backup-and-recovery/advanced-backup-and-restore-options-129834

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

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 7

Thanks a lot for kind reply and help for everyone i got my database back in normal.

I just did restore process again and worked nicely.

Also got to know about "Restart" option in restore command .

Thanks again Happy holidays

george sibbald SSC Guru Points: 104210 More actions · Answer 8

Bru Medishetty (12/14/2011)
george sibbald (12/14/2011)
you don't actually need to drop the database first
George, will it not fail as the physical database files exist and the rerun job cannot overwrite those files..

..with replace 🙂

---------------------------------------------------------------------

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 9

george sibbald (12/14/2011)
Bru Medishetty (12/14/2011)
george sibbald (12/14/2011)
you don't actually need to drop the database first
George, will it not fail as the physical database files exist and the rerun job cannot overwrite those files..
..with replace 🙂

Thanks George for clarifying.. With Replace was mot mentioned in your original reply, so I asked it..

Thanks...

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

joseph-397214 SSC Enthusiast Points: 103 More actions · Answer 10

I also face the same issue today. I droped the Db and restored it again.(in my case the backupfile was currupted thst why i got error.

joseph-397214 SSC Enthusiast Points: 103 More actions · Answer 11

logicinside22 (12/14/2011)
Is there any other way rather than to DROP?

🙂

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

GilaMonster (12/14/2011)
logicinside22 (12/14/2011)
Is there any other way rather than to DROP?
You can try RESTORE WITH RESTART, but unless the restore time is horrendous, probably unnecessary. The option's intended for absolutely huge databases.
http://www.sqlmag.com/article/database-backup-and-recovery/advanced-backup-and-restore-options-129834

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

radu.gheorghiu Old Hand Points: 343 More actions · Answer 13

Hi,

There is an article which I've just recently found, article which details everything you need to do in order to avoid getting stuck in RESTORING state next time you do your restore operation.

You can find the article here: http://sqlbak.com/blog/database-stuck-in-restoring-state/

It worked for me so I hope it will help you also.

Good luck!

~ Just some guy trying to tune queries ~

Michael G Ten Centuries Points: 1323 More actions · Answer 14

Hi All,

I had the same issue. My one db was also stuck "restoring". I thought I leave it in that state overnight (non-production) but it never recovered.

Thanks for pointing me in the right direction. This script worked for me:

USE [master]

RESTORE DATABASE [DatabaseName] FROM DISK = N'RestoreFilePathGoeshere' WITH RESTART, FILE = 1, NOUNLOAD, REPLACE, STATS = 5

GO

The with restart was the important part I guess.

Regards,

Michael

Michael Gilchrist
Database Specialist
There are 10 types of people in the world, those who understand binary and those that don't. 😀