Andrew Fraser DBA

10 Maintenance Plans

(Part of a series of SQL Server 2012 training course notes. Previous | Contents | Next)

Maintenance Plans perform tasks such as: Backup; Data File Maintenance, Log File Maintenance; Data Integrity Checks; Refresh Data Optimization Information if the automatic refresh option has been disabled.

Maintenance plans are commonly used to:

• Backup the databases
• Backup and then truncate the Transaction Logs
• Carry out any other standard database maintenance operations

Creating a maintenance plan will create related jobs and job steps.

Note that the SQL Server Agent service must be running for maintenance plan jobs to execute automatically.

Best to schedule each task with a separate schedule.

Three tasks are required in the maintenance plan

• Back up Database (Full)
• Back up Databsae (Transaction Log) – the T-Log will grow forever until backed up.
• Maintenance Cleanup Tasks – to delete old backup files from disk.

For both backup types, best to choose all databases – that way databases created in the future will be included automatically.

Also choose “Create a sub-directory for each Database”

Also choose “Verify backup integrity”

You must click on the “change” button at the bottom of each of these screens to set the schedule – easy to miss.

For small databases, schedule e.g. a full backup every week at 1am, a transaction log backup every hour, and clean up files older than 4 weeks every week at 00:00.

You will want to have two database maintenance cleanup tasks, one for bak files and one for trn (transaction log backup) files. That would be done as an edit to the maintenance plan after the wizard had created it. The two cleanup tasks can be kept in the same sub-task in the same schedule, by a copy/paste of the first cleanup task and than an arrow dragged between them.  By default the arrow is green for run-on-success only, that can be changed by right clicking on the arrow.

SQL Server Express Edition does not come with maintenance plans or SQL Server Agent, so the above method cannot be used for that.  Instead, use Windows Scheduler to schedule a dos batch command file containing T-SQL ‘backup database’ commands to get your database backed up.  This is, for DBAs, the biggest drawback of using Express Edition, but the added administrative burden is worth the saving in license cost.

Lab 10 – Create a new maintenance plan

(Part of a series of SQL Server 2012 training course notes. Previous | Contents | Next)