Search This Blog

How do backups work after changing the recovery model?

I copied a script off the net that changes the recovery model to bulk-logged then reorg or rebuilds indexes and then changes the recovery model back to the way it was. How does this affect my nightly full backup and hourly log backups? I guess I'm asking if a full back is required after the recovery model change. I don't want to find out that I can't restore a log backup because the defrag script ran since the last full backup.

You don't have to take a full backup after changing back from BULK_LOGGED to FULL. You need to realize that any log backup that contains a minimally logged operation (like an index rebuild in the BULK_LOGGED recovery model) cannot be used for point-in-time restore (i.e. you can't restore with STOPAT).

The best practice for doing what you want is:

1) Ensure there are no user operations running that cannot be regenerated

2) Take a log backup

3) Switch to BULK_LOGGED

4) Do the index rebuild

5) Switch back to FULL

6) Take a log backup

Basically you want to minimize the amount of time that you're in BULK_LOGGED.

Note that reorganizing an index with DBCC INDEXDEFRAG or ALTER INDEX ... REORGANIZE will be fully logged regardless of the recovery model.

Also note that BULK_LOGGED will prevent the log file from growing so much during an index rebuild but the log backup will NOT be smaller. It will contain all the data extents changed by the index rebuild operation.

If you ever crash while in the BULK_LOGGED recovery model, and you've done any minimally logged operations since you changed into BULK_LOGGED, you will not be able to backup the tail of the log. This is another reason why the only operations that should be running while in BULK_LOGGED are those that can be lost and regenerated.

For example

Time T1 - log backup taken in FULL

Time T2 - recovery mode switched to BULK_LOGGED

Time T3 - minimally-logged operation performed (e.g. index rebuild)

Time T4 - recovery mode switched to FULL

Time T5 - log backup taken

For a crash occurring between:

T1 and T2: backup the tail of the log, restore everything in sequence OK

T2 and T3: same

T3 and T4: log tail backup cannot be taken. Restores cannot proceed past T2

T4 and T5: same

After T5: backup tail of the log, restore everything in sequence OK

A log backup that contains a minimally logged operation can only be restored in its entirety - not using STOPAT. But, the minimally logged operation will be restored OK.

No comments: