• I try to make my maintenance plans as lazy as possible. I set them up to do the minimum necessary to make sure everything works well. That means index rebuilds when they are needed, not every night, for example.

    I generally do full backups every night for all production databases. I've yet to manage a database where a greater amount of data loss would be acceptible, and I've found that long periods of diff backups make for a higher probability of data loss. I could see, in some cases, an argument for weekly full and nightly diff and every-four-hour tran (for OLTP). Disregard the tran for databases that aren't transactional, of course (ones loaded nightly with ETL processes, for example). But the nightly full backups is more of a habit than otherwise, and works pretty well.

    On the laxy maintenance, part of the goal is to free up as much processing time as possible, because I'm used to servers that have to do a lot of work overnight to load up reporting tables and such, but also have to do maintenance overnight.

    - 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