MS SQL 2008 maintenance plans best practices

You do need a Disaster Recovery Plan and that pretty much determines best pactices... So, it really depends on your site and what you consider important - for example, the granularity of time for which backup processes need to be able to recover back to.

OK two different types of DB - the system databases and the user databases. And we are talking about the SQL backup utility (not the physical disk backup) and SQL maintenance plans - both of which are configured inside SQL. There are external maintenance tasks that can happen such as physical backup to external media, disk defrag, disk file locations etc, but assume we are talking just the SQL part.

System databases pretty much look after themselves in terms of transaction logs, and so forth. You do have to backup the "master" database for recovery purposes, and quite often event driven - for example, manual backup if just adding new security roles / restructuring security and so on. Model and MSDB, depends a bit on if you are actually using them, and typically, very much event driven. Exception could be MSDB which could be configured as having a full recovery model, where you are using e-mail regularly and want or need to retain history of transmissions - then you would be inclined to backup more regularly. TEMPDB, not typically a concern - it is a temp db after all, and cannot be backed up...

The system databases are typically SIMPLE recovery model and so transaction log backups are a waste of time - regular backup of MASTER, MSDB and MODEL (is used) can be scheduled as part of a regular nightly backup - they are not large so should not cause any large overhead. The exception is MASTER after manually updating, and possibly MSDB who's recovery model is configurable (as mentioned before).

Now, there are two other system databases : Resource Database - but cannot use SQL backup restore operations, need to use disk backup and manually recover. It is a read only database Mssqlsystemresource.mdf  and should be treated more as a binary (ie like an exe). The other is a distribution database. A database that exists only if the server is configured as a replication Distributor and that is another story altogether, but does require backup... It too, is simple recovery mode.

User databases is where the bulk of the consideration needs to be. It is after all a repository of your business transactions and information. This is also where the DR plays an important role - well at least the data part of the DR plan.

First thing to consider is the recovery model. Simple means minimally logged and pretty much keeps it's transaction log clean. Full recovery means that transactions are fully logged and offers a higher degree of recoverability if needed. The degree of recoverability is because transactions are held in the transaction log and backups of those transaction logs can be applied to your last full backup to bring the database back up to date (well at least to the last transaction log backup).

With FULL recovery, it is vitally important to have transaction log backups - you will need to manage them for a while to ensure correct sizing. Database files on disk are allocated a physical space and then consume that space with data as needed. If that space is unsufficient then it will grow (again an area which you can / need to manage with various options available). You will want to optimise that allocated space with data activity so that "autogrowth" is the exception rather than the norm.

For transaction log backups - only needed really in FULL recovery mode, you need to ascertain the granularity of time whereby the business could accommodate some dataloss. Now in theory you can recover up to the point of failure, however, your DR plan should consider the last transaction log backup as the point of recovery.

Transaction log backups are typically run as a multiple of quarter hour increments - e.g. every 15 minutes or every 30 minutes or hourly. I have also seen two-hourly and less frequent, but it begins to defeat the purpose and concepts of FULL recovery the more infrequent they become.

Both FULL and SIMPLE recovery databases should have a Full back up each night. In FULL recovery, transaction logs should be backed up at least hourly. I have seen smaller databases backed up more frequently, and one site used a SIMPLE recovery model, but backed up at each designated break during the day (e.g. start-of-day morning tea, lunch, arvo tea, end-of-day ) it seemed to fit their business quite well and did not have to worry too much about needing to manage transaction logs - but again, it happened to work well for the way in which they did business and they did have those "lulls" during the day (not that anyone had to stop working).

So, a nightly backup maintenance plan which is a full backup of all your databases. Then per dium plan for transaction log backups throughout the day.

Then the last one is periodic - typically weekly in a high growth, or "young" database, whereby indexes can be rebuilt, pages reogranised, stasticis recalculated. The more stable or longer established database, then these can possibly go monthly coinciding with end of month activities and possible history archives where bulk data movement may have an impact on the query plans.

There is another consideration, and that is the classic datawarehouse. They are normally historical in nature and largely read only. They should be simple recovery model with the nightly backup.