Post reply

Log Shipping 2005 read-only/standby mode question

  • November 21, 2007 at 11:19 am

    #180680

    Hello all,

    I'm confused about the differences between "no recovery mode" and "read-only/standby mode" in Log Shipping 2005. I know that "no recovery mode" is typically for DR and "read-only" is for querying. But can you take a "read-only" database and change the database back to a fully updateable db by executing "RESTORE WITH RECOVERY" like you could on a "no recovery mode" db?

    Thanks everyone and Happy Thanksgiving! 😀

  • November 21, 2007 at 12:48 pm

    #753203

    This looks like it would be a pretty easy one to test. Have you tried to do this yet? If so, did you get an error?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • November 21, 2007 at 1:24 pm

    #753208

    I just wanted to know if it was even possible because in order to test it I would have to setup a "roll-your-own" version of Log Shipping just to see if it would work.

  • November 21, 2007 at 2:02 pm

    #753218

    There are 2 types of read only modes. One is a database level option that you can set to make the DB read only. The other, and more relevent to this thread, is the state a database is left in when restored with STANDBY. If you have a database that was restored with STANDBY, it is in read only mode and in a consistent state as recovery has already taken place (unlike with NORECOVERY where no recovery has taken place and the database is unavailable). By executing your next restore with RECOVERY, it should place the STANDBY (or read only) database back into an updatable state. Make sense? The main difference between NORECOVERY and STANDBY is that STANDBY performs recovery and the data is available for viewing. This allows you to query the data to perform mid-restore analysis or just to leave around for reporting.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • November 21, 2007 at 4:47 pm

    #753243

    John,

    Now that you've explained what read-only actually does. My next question is when I change the database from read-only to an updatable database, how should that be executed TSQL wise?

    Example 1:

    RESTORE WITH RECOVERY

    Example 2:

    RESTORE <LAST Transaction Log - LSN) WITH RECOVERY

    Can you clarify this for me because it's racking my brain.

    Thanks for replying to this thread as well. I really appreciate your knowledge on this topic.

    -Dave:)

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

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