Post reply

Recover the database during undo \ redo phase

  • July 31, 2012 at 1:35 pm

    #261745

    I am trying to restore a huge database in the lower environment from prod backup,

    When I execute the below query It indicates the restore is complete

    SELECT percent_complete,* FROM sys.dm_exec_requests WHERE session_id = 57

    percent_complete =100

    But the Database is still in "Restoring" - State, When started the restore command I did execute the restore command "with recovery" and when i run the below

    sp_who2 active

    I can see one active restore spid, I belive the database is in undo \ redo phase.

    My first question is

    How do i confirm if its in undo \ redo phase.

    My second question is ,

    Can i Recover this database manually by issuing even though its in "Restoring" State

    RESTORE DATABASE dbname WITH RECOVERY

    As I am not concerned about open \ uncommited transactions, If I do that will the database will result in any corruption or in unusuable state.

    Any advice is appreciated.

    -Shan

  • July 31, 2012 at 1:49 pm

    #1519943

    What was the exact restore statement that you ran?

    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

  • July 31, 2012 at 2:11 pm

    #1519957

    Below is the statement...

    RESTORE DATABASE DBNAME

    FROM DISK = 'L:\SQL_Restore\dbbak.BAK'

    WITH

    STATS = 10, -- Show progress (every 10%)

    MOVE 'DBNAME' TO 'P:\Data01\db_data_file.mdf',

    MOVE 'DBNAME_log' TO 'P:\Logs01\db_data_file.ldf'

  • July 31, 2012 at 2:13 pm

    #1519959

    You'll just have to wait. Most likely the log is severely fragmented (lots of VLFs) and the recovery portion of the restore takes forever as a result.

    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

  • July 31, 2012 at 2:23 pm

    #1519967

    Thanks for your response, Is there anything that I could have done in the restore command to have the Database available faster, I mean not to move forward with undo \ redo state or there is no such thing, We just have to wait.

    -Shan

  • July 31, 2012 at 2:27 pm

    #1519972

    If you have the option you should plan for a piece meal restore.

    http://msdn.microsoft.com/en-us/library/ms177425.aspx

    Jayanth Kurup[/url]

  • July 31, 2012 at 2:39 pm

    #1519976

    Jayanth_Kurup (7/31/2012)

    If you have the option you should plan for a piece meal restore.

    How is that going to save on recovery time? Or total time to restore the entire database?

    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 3, 2012 at 7:29 am

    #1521381

    if there are readonly filegroups that do not have a critical role in bringint eh server up thenhe can restore the read write fielgroups to bring the DB online. sy for example the read only file group contains 500 GB worth of archive data, or contains archice tables that are not needed for functionaloty

    Jayanth Kurup[/url]

  • August 10, 2012 at 5:22 am

    #1524347

    That's a big if, since no such filegroups were mentioned. Also, if bringing the DB to a lower environment (dev/test), those read only filegroups would be needed, though the tables could probably be empty.

    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 9 posts - 1 through 8 (of 8 total)

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