Help - How to get a DB out of (...Restoring) state

Question

Post reply

Help - How to get a DB out of (...Restoring) state

Edvard Korsbæk

SSCertifiable

Points: 5994
More actions
March 5, 2014 at 4:09 am

#396371

Subject says most.
We took a backup of a database, and wanted to use that for a new database.
Created the new database
And suddenly the old database is inaccessible, and has been in ....restoring state for some hours.
I know that there must be some errors in the process, and that the description of what we have done can be wrong, but it is after 'best memory'
Best regards
Edvard Korsbæk

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

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

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

What was the backup command which you ran? What options did you specify?

RESTORING or RECOVERING?

Any entries in the SQL error log relating to this DB from about the time it became unavailable?

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

Edvard Korsbæk SSCertifiable Points: 5994 More actions · Answer 2

I am told: 'Right click and used the backup option'

Where do i find the error log?

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

That's not very useful 🙁

A DB won't go into the restoring state by itself, someone has to run a command to do so.

RESTORING or RECOVERING (important question)

Error log, from Object explorer in Management studio, expand out Management -> SQL Server logs and doubleclick on the current log.

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

Edvard Korsbæk SSCertifiable Points: 5994 More actions · Answer 4

I 100% agree with, that its not very usefull.

Start of the processs to duplicate a DB

And, its luckily so, that the employee made a full backup before making the new db.

Next step was to make a new db.

Next step was, that he used 'Tivoli' to restore from the old DB to the new db - And that went wrong. It was the old db that was restored, and that 'Hangs'

Reason cold be, that both the new DB and the old used same physical file.

So, what I have done now is to make a new 'test' db, and i have filled it with the data from the backup - That was trouble free.

So, now the problem is the one who is left in the 'Restore stage' - Delete it, and make a new one?

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

Edvard Korsbæk (3/5/2014)
Next step was, that he used 'Tivoli' to restore from the old DB to the new db - And that went wrong. It was the old db that was restored, and that 'Hangs'
Reason cold be, that both the new DB and the old used same physical file.

So he overwrote the old database with a new one?

Did he stop the restore part way through?

Does he remember what options he specified for the restore?

So, now the problem is the one who is left in the 'Restore stage' - Delete it, and make a new one?

No idea, answer my questions please?

Is the state RESTORING or RECOVERING?
Any entries in the SQL error log relating to this DB from about the time it became unavailable?

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

Edvard Korsbæk SSCertifiable Points: 5994 More actions · Answer 6

Thanks for your suggestions.

I ended op with:

Deleted the 'Hanging' DB

Made a new db, and restored it from the backup.

Renamed it to the 'old' name.

Up and running!

Each time i tried to make a new DB with the 'Old' name, I ended in '...restoring' state.

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 7

Just so you know, SQL serve won't allow two database two have the same physical file name at the exact same location, so it seems like the restore did an overwrite of your database and when they realized the mistake killed it but that left the old db hanging in restore state..

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Edvard Korsbæk SSCertifiable Points: 5994 More actions · Answer 8

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

So true....

Cath Trimble SSCarpal Tunnel Points: 4201 More actions · Answer 9

Are you restoring from the GUI?http://sqlblog.com/blogs/merrill_aldrich/archive/2013/03/15/ssms-2012-restore-gui-gotcha.aspx