Creating a good set of SQL maintenance plans

Here is an example of a good set of SQL maintenance plans for a server hosting the following:

- Dynamics GP production and test databases

- SSRS databases

First, the database properties need to be checked.

Determine which databases you will do log backups on.  These will need the recovery model set to Full.  All other system and user databases needs the recovery model set to Simple to prevent runaway log file growth.

Usually:

DYNAMICS and all production company databases are set to Full.

Mobiletech databases (RESCO databases) should be set to Simple (recommendation from WennSoft)
ReportServer databases are set to Simple

Next, determine which databases need to be backed up and how often

Do not backup tempdb.  It gets rebuilt when SQL Server is restarted, so you would never restore it anyway.  When I refer to system databases, I am only referring to master, model, and msdb.

Remember to backup ReportServerTempDB.  This is not rebuilt like tempdb and you will need it if you ever need to restore ReportServer.  You may not necessarily need the contents, but you will need the structure.

A typical schedule would be:

system databases - monthly - these databases rarely change

SSRS and non-production databases - weekly - SSRS databases do not significantly change unless new reports are deployed and test databases are typically more tolerant of data loss in a recovery situation.  Much of the time, test databases do not need backups as long as they are regularly refreshed from production so the log file does not grow too large.

DYNAMICS and production databases - nightly - these databases should also be Full recovery model with log backups throughout the day.

Counting the logs, this is 4 schedules, so you will need 4 plans.

Next, set up the plans

monthly - scheduled for 1st Sunday of the month at 10pm

1. Check database integrity task - no point in backing up a corrupt database

 - specify all system databases

2. Backup database task

- backup type Full

- specify all system databases

- choose create backup file for each database

- specify the backup folder

- set backup extension to bak

- check verify backup integrity

3. Rebuild Index Task

- specify all system databases

- take the defaults

4. Update Statistics Task

- specify all system databases

- take the defaults

5. Maintenance Cleanup Task

- choose backup files

- choose search folder

- specify the backup folder

- set backup extension to bak

- check delete files based on age

- set age to 3 months

6. History Cleanup Task

- select all options

- set age to 3 months

weekly - scheduled for Sundays at 11pm

1. Check database integrity task - no point in backing up a corrupt database

 - specify all non-system databases (you may be able to omit test databases)

2. Backup database task

- backup type Full

 - specify all non-system databases  (you may be able to omit test databases)

- choose create backup file for each database

- specify the backup folder

- set backup extension to bak

- check verify backup integrity

3. Rebuild Index Task - we will do this for all databases weekly

 - specify all non-system databases (you may be able to omit test databases)

- take the defaults

4. Update Statistics Task

 - specify all non-system databases (you may be able to omit test databases)

- take the defaults

5. Maintenance Cleanup Task

- choose backup files

- choose search folder

- specify the backup folder

- set backup extension to bak

- check delete files based on age

- set age to 12 weeks

daily - scheduled for Mon thru Sat at 11pm

1. Check database integrity task - no point in backing up a corrupt database

 - specify all non-system databases that are set to Full 

2. Backup database task

- backup type Differential

 - specify all databases that are set to Full

- choose create backup file for each database

- specify the backup folder

- set backup extension to dif

- check verify backup integrity

3. Reorganize Index Task 

 - specify all  non-system databases (you may be able to omit test databases)

- take the defaults

4. Update Statistics Task

 - specify all  non-system databases (you may be able to omit test databases)

- take the defaults

5. Maintenance Cleanup Task

- choose backup files

- choose search folder

- specify the backup folder

- set backup extension to dif

- check delete files based on age

- set age to 2 weeks

log backup - scheduled daily every 4 hours from 8am to 8pm

1. Backup database task

- backup type Transaction Log

- specify all databases that are set to Full

- choose create backup file for each database

- specify the backup folder

- set backup extension to trn

- check verify backup integrity

If a database is set to Full recovery model, you NEED TO setup transaction log backups.  If you forget this, your log file will grow until it hits the max size or fills up the drive. If you do not want to do transaction log backups on a database, set it to Simple recovery model. 

Manually run the plans in the following order;

monthly

weekly

daily

logs

This is important, not just to test them, but to establish full backups for the differentials to work off of.