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.
