0

Database is in full recovery mode and transaction log backup job is running every 15 minutes.

Will the following sequence of operations cause an issue with transaction log backups?

  1. Disable transaction log backup
  2. Take a full backup
  3. Change the recovery mode to simple
  4. Perform maintenance jobs like index rebuild, update stats etc...
  5. Change the recovery mode to full
  6. Enable transaction log backup

Or should I take a full backup after step 5 for transaction log backups to work?

Thanks.

Mayuran Parathalingam
  • 41
  • 8
  • What problem are you attempting to solve by doing this, as opposed to leaving your database in Full recovery model and letting the t-log backups run as normal, while doing the maintenance? – alroc May 15 '17 at 11:23
  • After maintenance job runs the size of transaction log backup file is huge. By this method transaction log backup file will be reduced as it wont contain log entries for maintenance tasks. – Mayuran Parathalingam May 15 '17 at 11:26
  • Perhaps you're doing more maintenance than is required? Full index rebuilds are typically *not* required in many environments. – alroc May 15 '17 at 12:29

1 Answers1

1

You break the tran log backup chain as soon as you do 3. You can restart a new log backups chain after 5 if you take a full backup. After 5 but without taking a full backup your DB remains in pseudo-simple mode, you cannot take any log backup, the log is in auto truncated mode

sepupic
  • 8,409
  • 1
  • 9
  • 20