none
How to open standby database for read/write?

    Question

  • Hello!
    I have log shipping setup between primary sql server and standby
    server. Both servers are MSSQL 2005 Enterprise.
    What are the steps to open standby for read/write (I need it to make
    primary at one point as a migration step)?

    Thanks,
    Lena

    Tuesday, July 19, 2011 6:49 PM

Answers

  • In Log shipping you can have two modes 1. Stand by 2.  in no recovery mode

    if you want to come out of standby mode and make database read/write  you simply need to run the commnad

    restore database <database_name> with recovery

    it will bring the database in read write mode. but will break your logshipping

     

    else you could bring it in no recovery recovery mode where you will not be able to do anything on the database.

     

     

     


    Harsh Chawla Personal Blog:- http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    • Proposed as answer by Peja Tao Thursday, July 21, 2011 8:23 AM
    • Marked as answer by Peja Tao Tuesday, July 26, 2011 8:24 AM
    Tuesday, July 19, 2011 6:59 PM
  • It was introduced in SQL server 2005.

     

    See, this is not a mandate to create the backup with copy only, I just recommended to do that just to avoid interrupts with the backups of the primary database. sometimes we take the backup and we forget to place the backup in the backup directories and when the issue happens we miss that t-log backup. 

    There is nothing to worry. you would take the backup with copy only using backup command mentioned in the same article or take a normal backup using GUI. In later case , keep the copy of the backup in the backup folder location so that if in any case issue happens you have complete set of backups. 

     

    I hope I am able to clear the confusion on it. 

     


    Harsh Chawla Personal Blog:- http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    • Marked as answer by Peja Tao Tuesday, July 26, 2011 8:24 AM
    Wednesday, July 20, 2011 6:38 PM

All replies

  •  

    Hi

    Restore Database <Database Name> with Recovery

     

    This will bring your stand by database to Read/Write mode


    Nag Pal MCTS/MCITP (SQL Server 2005/2008)
    • Proposed as answer by Peja Tao Thursday, July 21, 2011 8:23 AM
    • Unproposed as answer by Peja Tao Tuesday, July 26, 2011 8:22 AM
    Tuesday, July 19, 2011 6:54 PM
  • In Log shipping you can have two modes 1. Stand by 2.  in no recovery mode

    if you want to come out of standby mode and make database read/write  you simply need to run the commnad

    restore database <database_name> with recovery

    it will bring the database in read write mode. but will break your logshipping

     

    else you could bring it in no recovery recovery mode where you will not be able to do anything on the database.

     

     

     


    Harsh Chawla Personal Blog:- http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    • Proposed as answer by Peja Tao Thursday, July 21, 2011 8:23 AM
    • Marked as answer by Peja Tao Tuesday, July 26, 2011 8:24 AM
    Tuesday, July 19, 2011 6:59 PM
  • My standby is in no recovery mode currently.

    My standby will become primary at cutover time. We will be stopping everything that talks to current primary server, probably at that point I have to backup transaction log, copy it to standby server, apply it to make sure standby in sync with primary, and then run "restore database test with recovery" to open db for read/write. Are these steps correct?

     

    Thanks,

    Lena

    Tuesday, July 19, 2011 7:12 PM
  • Hi Lena

    Yes Perfect...!

    As you mentioned Just Make sure before bringing Stand by into Production all the Last Backup/Lastcopy/LastRestore Files are same.

    using Loghsipping Report or msdb..sp_get_log_shipping_monitor_info

     


    Nag Pal MCTS/MCITP (SQL Server 2005/2008)
    Tuesday, July 19, 2011 7:18 PM
  • Lena,

    you are right. 

    In summary, Here is what you will have to do:

     

    1. Make sure the pending t-log backups are restored after the last  restore

    2. take the final  t-log backup of primary and apply on secondry with recovery

     

     


    Harsh Chawla Personal Blog:- http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    Tuesday, July 19, 2011 7:21 PM
  • 2. take the final  t-log backup of primary and apply on secondry with recovery

     

    I can manually take t-log backup on primary, then restore it on a standby with recovery option. This will open my database. Correct? Just want to confirm one more time.

    Thanks again.

    Tuesday, July 19, 2011 7:46 PM
  • Yep thats correct.. Also make sure to disable the jobs of log shipping. And make sure u use copy only option with backup command. It will not break lsn chain and u will not need to change anything on the primary database. Its the safe approach.


    Harsh Chawla Personal Blog:- http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    Tuesday, July 19, 2011 8:51 PM
  • Hi Leena,

    If you follow backup/ restore approach then apart from all the listed steps , there is one more step which is about Logins. It may be possible that on secondary you don't have simialar logins exist as in primary & even if they exist then also there is high possiblity of orphan users due to SID mismatch.

    So my advise to you is to perform one more actvity, script out the logins, users & their permissions on primary & after restoration you can execute this on secondary. Occasionly you may get error if the login with same name exists, you can just ignore that.

    Also run sp_change_users_login 'report' to check for any orphan users.

    The other approach would be to perform the role change first,means make the secondary as primary & then gradually remove logshipping.

    You can use the below link for your reference if you're interested in following this approach:

    http://msdn.microsoft.com/en-us/library/aa215392%28v=sql.80%29.aspx

    Regards,

    Vishal.


    Regards, Vishal Srivastava
    Wednesday, July 20, 2011 11:42 AM
  • "And make sure u use copy only option with backup command"

     

    What do you mean by this?

    Can you provide syntax for this command?

     

    Thank you,

    lena

    Wednesday, July 20, 2011 3:17 PM
  • Check this article: - http://msdn.microsoft.com/en-us/library/ms191495.aspx

     


    Harsh Chawla Personal Blog:- http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    Wednesday, July 20, 2011 3:53 PM
  • I'm following the documentaion you ppovided how to backup transaction log with "copy only backup" option using Management Studio and I don't see the option from step #7 below. Am I missing something?

     

    To back up a transaction log

    1. After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

    2. Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.

    3. Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.

    4. In the Database list box, verify the database name. You can optionally select a different database from the list.

    5. Verify that the recovery model is either FULL or BULK_LOGGED.

    6. In the Backup type list box, select Transaction Log.

    7. Optionally, you can select Copy Only Backup to create a copy-only backup. A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. For more information, see Copy-Only Backups.

    Thanks,

    Lena

    Wednesday, July 20, 2011 4:05 PM
  • Checkbox just below the backup type is for copy-only Backup. Is that disabled in your case?
    Harsh Chawla Personal Blog:- http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    Wednesday, July 20, 2011 4:40 PM
  • Looks like this is a new feature in 2008. I'm running MSSQL 2005.

    So I cannot use this feature :-(

    Wednesday, July 20, 2011 6:07 PM
  • It was introduced in SQL server 2005.

     

    See, this is not a mandate to create the backup with copy only, I just recommended to do that just to avoid interrupts with the backups of the primary database. sometimes we take the backup and we forget to place the backup in the backup directories and when the issue happens we miss that t-log backup. 

    There is nothing to worry. you would take the backup with copy only using backup command mentioned in the same article or take a normal backup using GUI. In later case , keep the copy of the backup in the backup folder location so that if in any case issue happens you have complete set of backups. 

     

    I hope I am able to clear the confusion on it. 

     


    Harsh Chawla Personal Blog:- http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    • Marked as answer by Peja Tao Tuesday, July 26, 2011 8:24 AM
    Wednesday, July 20, 2011 6:38 PM