Post reply

Logshipping

  • September 20, 2010 at 2:35 am

    #103988

    Dear All,

    How I can change the recovery status of the secondary database from Norecovery to Standby mode or from Standby to Norecovery mode in Logshipping in SQLServer2005?

    Please advise.

    Thanks in advance.

    Ravichandra.

  • September 20, 2010 at 2:57 am

    #1223857

    when you restore the last log from primary to secondary, you have to provide the option as 'with standby'.

    It will make your secondary server database in standby mode and will accept any more logs to be restored.

    When you want to switch your secondary as primary, just restore the last log as 'with recovery'

    ----------
    Ashish

  • September 20, 2010 at 3:18 am

    #1223862

    Thanks Ashish.

    Actually my query is how to change the recovery status from standby mode to norecovery mode.

    I have configured logshipping in SQLServer2005 with sencondary database in Standby mode.

    Now,I want to change it to Norecovery mode.

    Regards.

    Ravichandra.

  • September 20, 2010 at 3:25 am

    #1223869

    what do you mean by norecovery, your database already in norecovery mode as its in standby mode.

    be familiar with the term of recovery and norecovery on below link

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

    ----------
    Ashish

  • September 20, 2010 at 3:42 am

    #1223874

    I have restored the secondary database with norecovery mode.

    Now,I want to change it to standby mode for reporting purpose( read-only mode ) i.e, to run select queries.

    Thanks and Regards,

    Ravichandra.

  • September 20, 2010 at 3:50 am

    #1223876

    When you restoring the last log,

    replace occurrence of "WITH NORECOVERY" with "WITH STANDBY = [some _filename]".

    [some_filename] = you can mention as 'c:\undo.txt' or whatever....

    ----------
    Ashish

  • September 20, 2010 at 4:09 am

    #1223888

    Thanks very much.

    One more doubt !!

    Is it same method have to use when changing from standby mode to norecovery? please

    Thanks and Regards,

    Ravichandra.

  • September 20, 2010 at 4:13 am

    #1223890

    your database is already in standby mode which means as well in norecovery mode.

    So let it be in standby mode because it will help you in 2 way :-

    1) will help users to read updated data(depends how frequently logs are getting restored)

    2) Allow you to restore logs.

    Or you have any specific requirement to change it back to norecovery mode?

    ----------
    Ashish

  • September 20, 2010 at 4:32 am

    #1223901

    Thanks, you are correct, it will be useful;but Just I want to know how to change it back to norecovery mode.

    Thanks and Regards,

    Ravichandra.

  • September 20, 2010 at 4:38 am

    #1223908

    yes, it will be same.

    whenever you want to change to norecovery, simply restore ...... with norecovery and then if you change your mind, simply restore log with standby... and so on...till you happy 😀 😀 😀

    ----------
    Ashish

  • September 20, 2010 at 5:00 am

    #1223922

    Thanks a lot Ashish.

    Regards,

    Ravichandra.

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

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