Koen Verbeeck (3/8/2011)
In the somewhat special case of ETL, I'd sometimes like to turn of logging.If a data load fails, the destination table can be truncated and the load can start over again, so you would not have to worry about inconsistency.
I'm only talking about the import layer here (the E of ETL). If updates are performed on datasets in the database, I would very much like logging, as I would like to go back to a previous state if necessary. But for imports, nah, I don't need logging 🙂
Would the Bulk Logged recovery model accomplish what you want on that?
You can also have a staging database, where you bulk import, et al, kept in Simple recovery, and just leave it out of the backup and maintenance plans. The log will grow to accommodate your imports, but it's simpler and less critical than a "real" database. If needed/wanted, keep that one on a cheap RAID 0 array. If it crashes and burns, replace the disks and re-run the create script from source control, and don't worry about recovery. Just make sure it's set up so that you don't lose anything that matters if you lose the whole database.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon