Maintenance plans are probably the easiest way to configure database and transaction log backups, as well as schedule regular maintenance tasks. Maintenance plans can have multiple steps with different schedules, so you might decide to have one all-encompassing plan, or several distinct plans.
Basic Plans
Each system will need individual consideration, but as a starting point I might use the following plans.
System databases – full backups : As the name suggests, this is a nightly full backup of the system databases. It should include the “Backup Up Database” step, and a “Maintenance Cleanup Task” step to remove old backups, files with an extension of “bak” older than X days.
System databases – weekend processing : A single job to do some of the following, depending on the needs of the installation.
- Check Database Integrity
- Shrink Database (Never schedule this!)
- Rebuild Indexes (or Reorganize Indexes)
- Update Statistics
- Clean Up History
User databases – full backups : Nightly full backups of all user databases. It should include the “Backup Up Database (Full)” step, and a “Maintenance Cleanup Task” step to remove old backups, files with an extension of “bak” older than X days.
User databases – TLOG backups : Backups of the transaction logs for the user databases. The schedule for these will depend on the activity of the databases and any requirements like log shipping, that may determine maximum lag time, and therefore backup schedule. Doing them every 30 or 60 minutes is probably fine for most databases. It should include the “Backup Up Database (Transaction Log)” step, and a “Maintenance Cleanup Task” step to remove old backups, files with an extension of “trn” older than X days.
User databases – weekend processing : The same as the job for the system databases, but you are likely to have more variety here, unless all the databases on the server have a similar requirement.
Considerations
As I said previously, these are a basic starting point, not a cast in stone rule. There are a number of things to consider which may affect what maintenance plans you need. Here are some things you may need to consider.
- One size does not fit all. The plans listed above assume all user databases have a similar requirement. In practice this may not the the case. There is nothing to stop you having database-specific plans or a mix-and-match approach.
- If user databases are not in FULL recovery mode, you don’t need to bother with the transaction log backups, but I would question why your databases are not in full recovery mode.
- Some people like to put the model and msdb databases into FULL recovery mode, some are happy with SIMPLE mode. If you do use FULL mode, you may want a transaction log backup of those also.
- If your user database become really big you might find nightly full backups impractical and have to switch to differentials. There is nothing to stop you doing full backups of some user databases and differentials of others. It totally depends on the databases you have on a system.
- The frequency of transaction log backups is related to the amount of loss off data you are willing to accept. Also, if you are using log shipping, you may wish to backup more frequently to reduce potential data loss.
- Some systems will get heavily fragmented indexes and need index rebuilds really frequently, while others are not affected. Doing them once a week as a starting point is probably OK, but as always you need to monitor and adjust as necessary.
- A rule of thumb is almost always a lie, but as a rule of thumb, never shrink a database and definitely don’t schedule it! Check out the links on this post to see why.
- Each database could have different requirements for how long you keep the backups. Adjust the plans accordingly.
Maintenance Plans Don’t Show
If the maintenance plans won’t show, complaining about “Agent XPs” not being
enabled, you have to run this from the Management Studio.
SP_CONFIGURE 'show advanced options',1; RECONFIGURE WITH OVERRIDE; SP_CONFIGURE 'Agent XPs',1 GO
For more information see: