July 12, 2012 at 1:11 am
Hi all,
Can you please explain what exactly happens in backend when we switch the Database(DB is in log shipping) recovery model from full to Simple?
--> Will LSN beark and logshipping stop working?
--> What if we there are no transactions executed in the time when the recovery model switched from Full To Simple and backagain to Full.
July 12, 2012 at 3:12 am
Your log shipping will break and you will need to take a full or differential backup on the primary, copy that to the secondary and restore it before you will be able to resume log shipping.
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
July 12, 2012 at 3:30 am
The first checkpoint will break things, as Gail noted. Doesn't matter if you have transactions.
July 12, 2012 at 4:21 am
Thanks Gail & Steve for answering my question...
Thanks
~Vishwanath
July 12, 2012 at 4:39 am
GilaMonster (7/12/2012)
Your log shipping will break and you will need to take a full or differential backup on the primary, copy that to the secondary and restore it before you will be able to resume log shipping.
The switch to simple will break things. No checkpoints are necessary.
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
July 13, 2012 at 9:27 am
Specifically, what will happen if you switch to simple recovery mode is that the backup job will start failing because you can't backup a database in simple recovery model. The secondary will still be fine, but eventually it will start raising alerts because there has been no backup to restore.
July 13, 2012 at 9:52 am
Robert Davis (7/13/2012)
Specifically, what will happen if you switch to simple recovery mode is that the backup job will start failing because you can't backup a database in simple recovery model. .
Penitentially specifically, you can't take a log backup of a database in simple recovery.
Switch back to full, log backup will continue failing until a full or diff backup is taken. However once the log backups are restarted, they can't just be copied to the secondary as restores will fail because LSNs don't match. Hence that full or diff will have to be copied over and restored.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply