April 8, 2010 at 1:35 pm
The decision was made to go to simple recovery during a large upcoming conversion ( 3 million + inserts). Should we take our preconversion full backup before or after switching to simple recovery?
April 8, 2010 at 7:38 pm
Indianrock (4/8/2010)
The decision was made to go to simple recovery during a large upcoming conversion ( 3 million + inserts). Should we take our preconversion full backup before or after switching to simple recovery?
I would recommend both - before the conversion just in case, and after the conversion and changing back to full recovery because it is required.
Once you change to simple, you have broken the log chain and will not be able to restore transaction logs past that point (if needed). To re-establish the log chain you need to perform a full (or diff) backup after changing the recovery model back.
If your decision was made to reduce the impact on the log file, then I would recommend changing to bulk-logged instead. This will not break the log chain - but, your transaction log backup following the conversion will be very large.
If your conversion is setup to convert ranges of data - instead of all at once, then you can run transaction log backups during the process (in full or bulk-logged recovery) to free up space in the transaction log while the process is running. You'll also find that this generally runs much faster than trying to convert 8 million rows all at once.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 8, 2010 at 7:53 pm
Thanks for the feedback. I was just getting at whether to:
a. switch to simple, take full backup, run conversion or
b. take full backup, switch to simple recovery, run conversion
Our conversion routines do many, small inserts so in simple recovery the expectation is that the log won't grow much. And yes I understand we'll have to take another full after the conversion before resuming full mode and Tlog backups.
April 13, 2010 at 6:11 am
How about this approach?
Before Conversion:
A. take final transaction log backup
B. execute CHECKPOINT
C. Set simple recovery mode
D. take pre-conversion Full Backup
After the conversion I know we must resume full recovery mode AND take a full backup before incremental ( transaction log ) backups can resume.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply