locked
Moving back database to restoring mode RRS feed

  • Question

  • Hi,

    Is there any way can we move back the online database to Restoring mode on SQL 2008 R2?


    Thanks & Regards, Karthikeyan

    Tuesday, May 14, 2013 11:02 AM

Answers

  • Then you can do the followings:

    1. On new PRD site leave the database in restoring mode (or in standby)
    2. Copy transaction log backups from current PRD site to new PRD site and DR site simultaneously and restore with norecovery. Both are in restoring mode
    3. When you have to finally switch from current PRD to new PRD then just recovery the database on new PRD server (takes only some seconds)
    4. Create a log backup on new PRD server and resotre it on DR with norecovery
    5. Then you can set up mirroring between new PRD and DR server

    If I understand the goals. You can not use simultaneously the old PRD and new PRD server so they should not be online in the same time.


    m@te

    • Proposed as answer by farkas.mate Tuesday, May 14, 2013 12:12 PM
    • Marked as answer by KarthikNila Thursday, May 23, 2013 5:45 PM
    Tuesday, May 14, 2013 12:12 PM

All replies

  • Hi, can you explain the reason why you might want to change the state to Restoring?  You can change the state through SSMS or script to several different states, but not explicitly to Restoring.  From Books Online for ALTER DATABASE SET, you can set the following:

    <db_state_option> ::=
    { ONLINE | OFFLINE | EMERGENCY }

    <db_update_option> ::=
    { READ_ONLY | READ_WRITE }

    <db_user_access_option> ::=
    { SINGLE_USER | RESTRICTED_USER | MULTI_USER }

    Thanks,
    Sam Lester (MSFT)


    http://blogs.msdn.com/b/samlester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.



    Tuesday, May 14, 2013 11:04 AM
  • As Sam suggested please provide the reason to change the status.

    If you wish to make the database non-operative, you can take the DB to OFFLINE status.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, May 14, 2013 11:06 AM
  • Hi,

    We are having SQL 2000 runing on PRD systems, planing to migrate it into new SQL 2008 server with DR also. Planning to use database mirroring between new PRD and DR server.

    The new PRD and DR site located on different locations which needs the database backup to be sent via TAPE and takes 1-2 days.

    I'm exploring to do the below,

    1. Backup existing PRD and send it to new PRD site

    2. Restore the backup into the new PRD site

    3. Backup new PRD site and send it to DR site via TAPE which will reach next day EOD.

    4. Restore it on DR site with no recovery for configuring mirroring, Up to that the transactions on existing PRD will go that;s the business need.

    5. Planning to take transactional log backup on existing PRD server and apply it on new PRD server, it fails since the database already restored on with recovery.

    That's the reason i'm looking on option to make the online database state to RESTORING mode again.

    Upon this done, i planned to apply the transactional backup in new PRD env, take new transactional backup from new PRD server and send it to DR site through LAN(it should be small size) and confgiure mirroring.

    Not sure this startegy works, wanna giva a try. Please let me know or how best we can manage this scenariao. The issue here is the data size (not to be transfered through LAN, only through courier that takes 1-2 days).


    Thanks & Regards, Karthikeyan

    Tuesday, May 14, 2013 11:33 AM
  • Then you can do the followings:

    1. On new PRD site leave the database in restoring mode (or in standby)
    2. Copy transaction log backups from current PRD site to new PRD site and DR site simultaneously and restore with norecovery. Both are in restoring mode
    3. When you have to finally switch from current PRD to new PRD then just recovery the database on new PRD server (takes only some seconds)
    4. Create a log backup on new PRD server and resotre it on DR with norecovery
    5. Then you can set up mirroring between new PRD and DR server

    If I understand the goals. You can not use simultaneously the old PRD and new PRD server so they should not be online in the same time.


    m@te

    • Proposed as answer by farkas.mate Tuesday, May 14, 2013 12:12 PM
    • Marked as answer by KarthikNila Thursday, May 23, 2013 5:45 PM
    Tuesday, May 14, 2013 12:12 PM
  • Hi M@te,

    For configuring database mirroring, we need a valid backup from primary(here new PRD) to mirror(DR) site right? If we are restoring backup on new PRD server with no-recovery, how can we take backup of it. please clarify.


    Thanks & Regards, Karthikeyan

    Tuesday, May 14, 2013 12:58 PM
  • to configure Mirroring

    you database must be in FULL Recovery mode, then take a FULL Backup followed with log Backup and restore it on the mirrored Server


    Ramesh Babu Vavilla MCTS,MSBI

    Tuesday, May 14, 2013 1:14 PM
  • You don't need a full backup of the new PRD only a transactional backup after recoverying it. If you restore this new transaction log on DR database then it will be prepared for configuring mirror.


    m@te


    Tuesday, May 14, 2013 3:21 PM
  • Hi Mate,

    1 full backup needed from new PRD site to configure mirroring on DR site right? If i kept the new PRD database on RESTORING mode how can i backup and sent it to DR site.


    Thanks & Regards, Karthikeyan

    Tuesday, May 14, 2013 5:07 PM
  • No! As I wrote previously you DO NOT NEED to create a full backup from new PRD site to configure mirroring because you have a prepared database (in restoring state) on DR site.

    Please read attentively my first post in this thread and do it based on that step by step!


    m@te

    Tuesday, May 14, 2013 7:56 PM
  • Hi Mate,

    Its worked for me... Thanks a lot....

    One more query, Is backup file mandatory for configuring DB mirroring or we can do the same with mdf/ndf/ldf files.


    Thanks & Regards, Karthikeyan

    Thursday, May 23, 2013 5:47 PM