Maintenance policy templates

The first step of the maintenance policy creation wizard displays a list of 7 maintenance policy templates for different types of databases. 3 of them use Ola Hallengren scripts.

To create an empty policy, select the Create an empty maintenance policy option on the list.

Maintenance policies should include databases with similar characteristics: frequency and intensity of data input/modification, size, available database maintenance window. For example, if data are loaded into a database once a week, it shouldn’t be included in the same policy with databases with frequently backed up transaction logs and daily updated indexes, which is a sign of day-to-day intensive data input. Otherwise, it will result in excessive load on the SQL server and a large number of unnecessary backups, which, in turn, will increase the backup restoration time in case of database corruption.

The database recovery model defines the date to which the data will be restored to in case of an accident. For instance, the Full recovery model implies that the data can be restored virtually to the instant when the system crashed or any other preceding moment, given that there is a full backup and the necessary backups of the transaction log. When the Simple model is used, data can only be recovered to the date of the most recent backup. See more details https://msdn.microsoft.com/en-us/library/ms189275.aspx

We recommend using the Full model for databases with daily data input.

By default, all templates include tasks for servicing system databases. Please bear in mind that the program creates several maintenance policies for one SQL server. In this case, after you create a second and subsequent policies, you should delete unnecessary database maintenance tasks from the QMB jobs.

Maintenance policies based on QMB Jobs

Maintenance policies based on Ola Hallengren stored procedures

Maintenance policy templates are not editable. The program supports copying of policies between SQL servers. This allows you to create a policy once and copy it afterwards.

Wizard steps

Maintenance policy QMB Jobs

General Information

Maintenance Policy, Scripts, and Tasks

Frequently Asked Questions

Policy name	Recovery model	Creation of a full backup	Creation of a differential backup	Creation of a transaction log backup	Features
Policy for small or medium-sized OLTP databases with intensive data input	Full	Monday	Every night	Every 30 minutes	Indexes: defragmentation every night, full rebuilding once a week Statistics: update modified statistics every 30 minutes, full update every night Database integrity check: every night More about the policy
Policy for large OLTP databases with intensive data input	Full	Monday	Every night	Every 30 minutes	Indexes: defragmentation and index rebuilding once a week Statistics: update modified statistics once a week Database integrity check: once a week More about the policy
Policy for large databases with minor data changes	Simple	Monday	Every night	-	Indexes: defragmentation and index rebuilding once a week Statistics: update modified statistics once a week Database integrity check: once a week More about the policy
Policy for archive database and data warehouses with occasional updates	Simple	Sunday	Wednesday	-	Indexes: defragmentation and index rebuilding once a week Statistics: update modified statistics once a week Database integrity check: once a week More about the policy

Policy name	Recovery model	Creation of a full backup	Creation of a differential backup	Creation of a transaction log backup	Features
Policy for small or middle-sized OLTP databases with intensive data input	Full	Monday	Every night	Every hour	Indexes: defragmentation every night, full rebuilding once a week Statistics: update modified statistics every hour, full update once a week Database integrity check: every night More about the policy
Policy for large OLTP databases with intensive data input	Full	Monday	Every night	Every hour	Indexes: defragmentation and index rebuilding once a week Statistics: update modified statistics every hour Database integrity check: once a week More about the policy
Policy for large databases with a minor data modification rate	Simple	Monday	Every night	-	Indexes: defragmentation and index rebuilding once a week Statistics: update modified statistics once a week Database integrity check: once a week More about the policy