Change Recovery Model of a Replicated Database

Question

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

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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