January 15, 2012 at 10:08 am
I have two databases in my SSE instance - one is the live data, the other is a development model. Periodically, I wipe the dev DB clean and copy all live data into it, as my coding experiments trash the data in the dev copy. For backup purposes, both data and structure are important in the live version, but only the structure in the dev version. When I run the copy routine, sometimes several times per day, my dev log grows, quite a lot. It's not huge as far as disk space limitations go, but considerably larger than necessary for the actual needs of the DB.
Also, the dev DB occasionally beomes the new live DB, when some structural changes have been properly tested and are ready to implement. I detach, copy, rename and re-attach the databases, and of course, the unnecessarily bloated log from the dev DB becomes the new log for the live DB.
Despite having read Gail Shaw's excellent articles on Recovery Model and Managing Transaction Logs, I'm still a little vague on the meaning and functionality of the various methods of logging and backing up. Can someone suggest a procedure on how to manage this, where the data in the dev DB is of no consequence, and I have zero interest in being able to re-create the data, or any of the transactions involved in copying the live data into the dev DB? Should I switch logging levels, recovery models, make backups of the dev and toss them immediately, ...?
January 15, 2012 at 10:35 am
I think you might do well to set the recovery model of the dev database to "simple", and make sure that the last thing your copy routine does is call CHECKPOINT.
Also, try to arrange that your copy routine doesn't try to copy the whole production database to dev in a single transaction; if it does, you may want to shrink the dev db log to whatever size is a sensible log size for the production db before making the dev db into the production db. Shrinking the log is generally anethema, but here it might make sense).
When making the dev database the production database, you will need to set the recovery model (or the new production database) to whatever is needed for production and take a full backup immediately.
Tom
January 16, 2012 at 7:15 am
So, let me see if I have this straight:
Dev DB
Simple Recovery mode
For each reload cycle:
- Empty and refill from live
- Checkpoint (to keep from bloating log)
- Backups not necessary, except as I see fit to preserve whatever structure changes I have made, and probably best done between emptying and refilling, to keep backup file size down (not backing up useless copies of data)
When making into live version:
- Final data import cycle
- Change to Full Recovery mode
- Maybe shrink log, if it has grown beyond reasonable needs during development
- Backup immediately, before putting into service
Live DB
Full Recovery mode
Backup as appropriate for business needs
Does that seem like a reasonable plan? What I would actually like is to not log the repeated imports at all, but there doesn't seem to be any way to make that (not) happen, according to Gail's article on recovery models.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply