SQL Server Performance

Log Shipping & Recovery Model

Discussion in 'SQL Server Log Shipping' started by aki_aki, Apr 18, 2006.

  1. aki_aki New Member

    I am doing log shipping, Now there is some data that i need to insert into my master & then use that for some hours & get rid of it, I dont want the secondary server to get this data , cause later on I am gonna delete it anyways,
    So can i change the recovery model of my DB to simple , perform the operation & then switch it back to Full Recovery , & expect the Log shipping to work fine ????
  2. SQLDBcontrol New Member

    quote:Originally posted by aki_aki

    I am doing log shipping, Now there is some data that i need to insert into my master & then use that for some hours & get rid of it, I dont want the secondary server to get this data , cause later on I am gonna delete it anyways,
    So can i change the recovery model of my DB to simple , perform the operation & then switch it back to Full Recovery , & expect the Log shipping to work fine ????


    There shouldn't be any problem with just leaving log shipping running as it is. As you say, the data you insert will be log shipped. And later on, the data that you delete will also be log shipped (so to speak).

    Switching to simple recovery mode won't help as that will break log shipping given that you won't have any log backups to ship anymore. And once that happens, you'll have to re-initialise the secondary database.

    Karl Grambow

    www.sqldbcontrol.com
  3. satya Moderator

    Master database means the SQL Server system database 'master' or the user database which you've enabled for log shipping?
    If it is for system database you need not worry as the recovery model is SIMPLE always and only user databases that are added in log shiping will be log shipped.

    In the above case I would suggest to stop log shipping and perform local transaction log backup until the intermittent data manipulation and then restore full database to the secondary server to continue the log shipping.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. vbkenya New Member

    You don't want to play with the Log Shipping Config unless you are willing to re-initialize the whole thing in case things go wrong.

    I would suggest you follow SQLDBControl's suggestion and leave things the way they are.

    Why would you want to play with a live production database? I would normally restore a backup of the database on another machine and play with it without worrying about messing up anything.

    Nathan H. Omukwenyi
    MVP [SQL Server]

Share This Page