Change Recovery Model of a Replicated Database

• dbychen

  SSCommitted

  Points: 1889

  More actions

  May 20, 2014 at 5:45 am

  #293474

  Hi All,

  I need to change the recovery model from SIMPLE to FULL for a database configured with snapshot replication. The database servers as a publisher in the replication. Is there a quick way to make this change without affecting the current replication configuration? I would appreciate if someone can advise on this with a detail direction.

  Thanks,

  Yichang

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 20, 2014 at 6:16 am

  #1714889

  Just change it. Database settings aren't replicated and recovery model has no effect at all on replication.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• dbychen

  SSCommitted

  Points: 1889

  More actions

  May 20, 2014 at 7:08 am

  #1714917

  Thanks Gail for the reply.

  The system does not allow to proceed in SSMS. A prompted error refers to log_reuse_wait_desc column of sys.databases. The value of this field relevant to the database is "replication". It seems that the replication setup prevents from changing the recovery model. I am unsure if this is the case.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 20, 2014 at 7:10 am

  #1714918

  Replication does not prevent a change in recovery model.

  ALTER DATABASE <database name> SET RECOVERY FULL

  If it throws an error, please post the exact error message.

  Curious, why are you setting the recovery model to full?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• xxxx

  SSC Enthusiast

  Points: 134

  More actions

  January 26, 2017 at 7:29 pm

  #1925221

  GilaMonster - Tuesday, May 20, 2014 7:10 AM

  Replication does not prevent a change in recovery model.ALTER DATABASE <database name> SET RECOVERY FULLIf it throws an error, please post the exact error message.Curious, why are you setting the recovery model to full?

  Hi, what's was the answer ? Can we change the recovery model from simple to full without reconfigure the replication? I need to do it for the RPO. The replication is not for all table, that's why I need it the backups logs.

  THanks. 

  Manuel