Post reply

database stuck in restoring state

  • August 4, 2016 at 8:24 am

    #312282

    Here is my scenario:

    I have a test server dev1 with database e. I have a production server p1 with database e.

    I wanted to restore database e from p1 to dev1 as e_tst, so I could do some specific testing against production data.

    I backed up e on p1 using management studio. I then went to restore the backup on dev1 using management studio. I changed the destination to e_tst. I changed the file names to e_tst for the database and log files. I didn't choose "with replace". My premise was the e on dev1 would be unaffected. I started the restore - it took a long time to run - and said it was working on the tail log. When it finished it said that e was in the restoring state - and it remains that way. I didn't think anything I did would impact the original, e. But, obviously I am wrong, so I must have done something wrong, or don't understand the process.

    What could I have done wrong and how can I fix? I do have a backup of dev1 e.

    Thanks

  • August 14, 2016 at 1:53 pm

    #1895360

    I don't know why no one answered this for you and I hope you weren't waiting on the answer.

    Step one would be to restore your dev database and get the backups on that rolling again.

    On the "why" it got overwritten, it's not possible for us to tell from here. If you did it manually through a GUI, there are lots of places where something could have gone wrong. That's why I never hit the OK button on such a thing. I always script it out and then cancel the action so I can study the script to make sure nothing will go haywire. The fact that it said it was working on a tail log backup is the first indication that the restore was pointed at your dev database instead of a new database. The database being stuck in recovery suggests the same. It's probably waiting for you to restore the tail log.

    --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)

  • August 14, 2016 at 2:44 pm

    #1895361

    It didn't get overwritten.

    The default, when restoring a copy of an existing DB that's in full recovery model, is to first take a tail-log backup of the original. Bad default, I suspect it's there for when restoring over an existing DB and the default's too broad.

    To get the DB out of the restoring state, all that would be needed would be

    RESTORE DATABASE <database name> WITH RECOVERY;

    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

  • August 16, 2016 at 11:35 am

    #1895728

    Thanks Gila, and thanks Jeff. I didn't get initial replies here so I researched it further and found the indications to execute the restore database <Name> with recovery, as you reference below. This worked. After I did this, database e, the original, was intact and unchanged / not overwritten.

    So as per your explanation it sounds like the tail log backup executed against the original db, e, even though I was restoring to a new database e_tst, not overwriting? Therefore it does sound like the default is "too broad" insofar as it is deciding to perform a backup up the log of e, when I am not trying to perform any operation on e - only the source of the restore is from e.

    GilaMonster (8/14/2016)

    It didn't get overwritten.

    The default, when restoring a copy of an existing DB that's in full recovery model, is to first take a tail-log backup of the original. Bad default, I suspect it's there for when restoring over an existing DB and the default's too broad.

    To get the DB out of the restoring state, all that would be needed would be

    RESTORE DATABASE <database name> WITH RECOVERY;

  • August 16, 2016 at 2:54 pm

    #1895786

    There's a reason I teach my devs to script out the restores before running them and check what the script does. 🙂

    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

Viewing 5 posts - 1 through 4 (of 4 total)

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