Replication and Backups RRS feed

  • Question

  • Hi,

    Running SQL Server 2008 R2 SP2.  Server handles distributor and publisher roles for merge replication topology (endpoint subscribers replicate directly with this server).  All system and user databases are set to full recovery mode.  The database backups (type D in backupset table) are conducted once daily at 9:00 PM.

    Based on the current scenario I have a few questions:

    a) Because these are full database backups does that mean we have no options to shrink the transaction log?  It is growing considerably and (my understanding is) we cannot shrink it because we're not doing log backups.

    b) If we were faced with a recovery scenario (keeping in my the database is replicated), would it be as simple as restoring the publication and distribution databases and having subscribers sync?  Or are there specific items we need to take into consideration when restoring a replicated database?

    c) Would there be any advantage (or disadvantage) to take a full database backup once a week (say Sunday night) with a log backup the other 6 days?  Would we achieve anything different using this strategy other than what we're currently getting?

    d) Would there be any arguments for changing the recovery model of our databases?

    Any help is greatly appreciated!

    Best Regards


    Friday, August 24, 2018 7:19 PM

All replies

  • You should be doing log backup if your databases are in full or bulk recovery models. Otherwise you will experience out of control log growths.

    If you log ship your databases to another server you can use the keep replication switch for the final log. Your publications will be restored.  You will need to restore the msdb, master and log ship the distribution database. Then if you rename the log ship destination server (secondary) as the name of the old server (source/primary) everything will work.

    You are welcome to backup and restore any database in any manner you want but you will need to reinitialize your subscribers unless you use the log ship option outlined above.

    You will need to use full recovery model (or bulk logged recovery model) if you want to prevent out of control log growths.

    Friday, August 24, 2018 7:53 PM
  • Hi Hilary,

    Appreciate your response.  We don't do log shipping, as such how would our restore strategy be affected with regard to replication?


    Friday, August 24, 2018 8:16 PM
  • You can use your restore strategy.
    • Edited by Abbottee Monday, August 27, 2018 5:16 PM
    Monday, August 27, 2018 5:12 PM
  • First, you're one Service Pack behind. The latest SP for 2008 R2 is SP 3. It's always safe to be on the latest SP.

    a) Since you are running under Full recovery model, transaction log won't truncate until you run log backups. Replication works fine even in simple recovery model but you'd have to watch for the potential data loss etc if you run under simple recovery model. In short, you'd have to start running log backups to efficiently manage the t-log. In full recovery model, only a log backup truncates the relevant portion of the transaction log so it can be reused for logging subsequent transactions.

    b) Recovering databases participating in replication gets trickier if you want o keep the replication cofiguration intact. The following article details about what DBs need to be backed up and what the restore sequence should look like. 

    On the other hand, if the application is not that critical and you want a straightforward recovery, depending on which DB is being recovered, you can go with the recovery plan. for example, if it's the publication DB, then restore it from a last known good backup (full + (diff) + log) followed by re-initialization of subscriptions. Of course, you want to salvage data from the subscriber prior to re initializing since it'll overwrite articles with publication DB. Things may also change if you have Merge properties such as "download only to subscriber allow subscriber changes" etc set for merge articles. 


    c) Yes, doing log backups will let you to recover the database to a specific point-in-time. Also, it maintains and keeps log file from growing.

    d) Since you're already running under full recovery model, you should be good. Just make sure that Log backups are part of your backup policy. 

    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Monday, August 27, 2018 9:27 PM