Database administrators tend to use various scripts or applications, to make the daily SQL Server database maintenance task easier.

Some more experienced administrators prefer to design and use their own scripts for these tasks. The scripts are usually designed to fit the requirements imposed by the specific environment. After the scripts are thoroughly tested, they often get included in a maintenance plan, or SQL Server agent job to automate their execution. Such solution is usually optimal for some demanding environments like high traffic servers or databases that are still in development. Designing these solutions usually take time, and require an experienced DBA skilled in T-SQL or PowerShell scripting.

On the other hand, some administrators like the simplicity offered by the 3^rd party applications. The companies that design these applications invest a great amount of effort to make their apps simple to use and feature-rich at the same time. These applications usually come with the nicely-packed interfaces and do not require much of the experience from their users. The drawback to this solution is certainly its price.

Ola Hallengren’s maintenance solution could be considered a hybrid of these two solutions, as it takes the best perks from both options: it is simple, highly customizable, and it’s free. As it takes many parameters into account, it can be configured to fit most demanding environments. But it can also be used successfully with the default settings on simpler servers and databases. The jobs created by the Maintenance Solution automatically process all user databases on the server. So be aware of that if databases on your server require different maintenance scenarios.

Ola’s SQL Server Maintenance Solution consists of various scripts that create jobs and stored procedures once installed on the SQL Server. The entire solution was designed by Ola Hallengren, an MCITP database administrator and database developer, who has been working with SQL Server since 2001. The solution consists of three parts:

1. SQL Server Backup
2. SQL Server Integrity Check
3. SQL Server Index and Statistics Maintenance

This article series will cover all three parts of this maintenance solution. In this part, installation and SQL Server Backup solution will be described in more detail.

Installation

Before starting, make sure that all software requirements have been met. This should not be a problem, as the solution supports all Windows versions of SQL Server, starting from SQL Server 2005. It is also supported on all editions of SQL Server. Support for SQL Server 2017 on Linux is still work in progress.

To install the complete maintenance solution, perform the following steps:

1. Navigate to Ola Hallengren’s site, and download the file MaintenanceSolution.sql

2. Save the file anywhere on the hard drive, and open it in SSMS or any other script editor

3. Locate the following lines of code near the top of the script (around line 32):

   1 2 3 4 5

   SET @CreateJobs          = 'Y'         SET @BackupDirectory     = N'C:\Backup' SET @CleanupTime         = NULL         SET @OutputFileDirectory = NULL         SET @LogToTable          = 'Y'

4. If needed, change the settings for the following variables:

   • Replace the value of the BackupDirectory variable (’C:\Backup’) with the preferred backup path. The provided path will be used as a root directory for storing backup files. The network share can also be used, but in this case, both SQL Server and SQL Server Agent services need to be run under a domain account with full control of the directory and the network share
   • Set the integer value for the CleanupTime. This value specifies how long (in hours) will each backup file be kept on the drive. After the specified time passes, the file will be deleted automatically. If default NULL value is left in the script, the backup files will never be deleted automatically
   • OutputFileDirectory variable: this variable specifies the path for the log files that will be created after each job is run. Specify a custom path if needed or leave the NULL value to use the default SQL Server error log directory
   • LogToTable variable has only values for Yes and No. Default value is set to Yes, and leaving this value as is will log the results of each created job in the CommandLog table
   
   

5. Open SSMS, and connect to the server where you want to install the solution

6. Make sure that the SQL Server agent is installed and running. To start the SQL Server Agent, expand the node of the connected server, right-click on the SQL Server Agent, and click on Start in the context menu

   

7. Execute the MaintenanceSolution.sql against the server. The script creates one table (dbo.CommandLog), one function, and four stored procedures in the master database. It also creates 11 pre-created SQL Server Agent jobs. Each of the created jobs will be discussed separately

   
   

Installed components

As mentioned above, the script installed the CommandLog table, four stored procedures and 11 agent jobs.

1. CommandLog table is used for logging. Parameters such as: DatabaseName, Command, CommandType and StartTime are logged for each operation, while the SchemaName, ObjectName, ObjectType,  StatisticsName, PartitionNumber and ExtendedInfo only get logged for the specific operations

   

2. Stored procedures determine the settings for each created job

   • CommandExecute stored procedure is the crucial component of the maintenance solution, as it is used by all other created procedures and jobs. Its main purpose is executing various T-SQL commands, recording the starting and ending times for each command to the log, as well as the final operation outcome
   • DatabaseBackup stored procedure is designed to manage all backup maintenance tasks. It is usually used with the default settings, but in some specific cases, parameters like Verify, Compress, ChekSum can be changed from ’N’ to ’Y’ in order to verify, compress, or run cheksum on the created database backups
   • DatabaseIntegrityCheck stored procedure is used for integrity check jobs. More details will be provided in the upcoming article of the SQL Server maintenance solution series
   • IndexOptimize stored procedure is used by IndexOptimize job. All its parameters will be elaborated in the third article of the maintenance Solution series
   
   

3. SQL Server Agent jobs are the final product of the script. All jobs are created without a schedule, and these need to be set manually by the user. There are total of 11 jobs: 4 backup jobs, 2 database integrity check jobs, one index optimize job, and 4 cleanup jobs. This article will focus on the backup jobs

The names for the backup jobs are pretty much self-explanatory. To confirm that these jobs are configured properly, it is best to run each job separately.

DatabaseBackup – SYSTEM_DATABASES – FULL

As its name suggests, this job creates a full database backup of each system database. Since all system databases use Simple Recovery Model, it is not possible to backup their transaction logs. To run this job, right click on it in Object Explorer, and click on Start job at step… option in context menu.

If the job was configured properly, the success message will be displayed:

To confirm that backup files were created, navigate to the backup folder that was specified in the script. The backup job created a new folder on the specified path:

The folder is named after the server where jobs were configured (DOMENATOR$MAIN). The backup job created files in separate folders with the following structure:

[Root]\Server$Instance\Database\BackupType\Server$Instance_database_BackupType_Date_Time.bak

In this example, the full path to the backup file is:

E:\Backup\DOMENATOR$MAIN\master\FULL\DOMENATOR$MAIN_master_FULL_20171201_081822.bak

DatabaseBackup – USER_DATABASES – FULL

Creates one full database backup file for each of the user databases, regardless of the recovery model. The job is run from the context menu in the same way as in the previous example. A success message is generated by the completed job.

Folder hierarchy and backup file naming rules are also identical to the previous example: each database has its own folder that further contains a folder for each backup type. As this job creates full database backup files, the full filepath to the AdventureWorks2014 backup file is:

E:\Backup\DOMENATOR$MAIN\AdventureWorks2014\FULL\DOMENATOR$MAIN_AdventureWorks2014_FULL_20171201_094214.bak

DatabaseBackup – USER_DATABASES – DIFF

The job creates differential backups of all user databases. Make sure to create at least one full database backup for user databases before running this job, or else the job might fail. Same folder hierarchy and naming rules apply to this job. For example, the differential backup created by this job for the AdventureWorks2008 database will be located at:

E:\Backup\DOMENATOR$MAIN\AdventureWorks2008\DIFF\DOMENATOR$MAIN_AdventureWorks2008_DIFF_20171201_101016.bak

DatabaseBackup – USER_DATABASES – LOG

This job creates the transaction log backup of all databases that use Full or Bulk-logged recovery models. Databases in Simple recovery model are skipped automatically. Here is the path of the transaction log backup created by the job for the AdventureWorks2014 database:

E:\Backup\DOMENATOR$MAIN\AdventureWorks2014\LOG\DOMENATOR$MAIN_AdventureWorks2014_LOG_20171201_103452.trn

Automating the process

In order to be able to run any of the created jobs automatically, and use the backup solution to its full potential, it is necessary to configure the schedule for each job. This step is left out of Ola’s script deliberately, as each environment requires specific backup schedule that heavily depends on predefined RPO (Recovery Point Objective) and RTO (Recovery Time Objective).

To define a schedule for any of the four backup jobs, perform the following steps:

1. In Object Explorer, expand SQL Server Agent and Jobs nodes. Right click on the backup job to open the context menu, and click on Properties. In this example, the schedule will be configured for DatabaseBackup – SYSTEM_DATABASES – FULL job. The schedule is configured in the same way for all other backup jobs from the solution

   

2. The Job Properties window is opened. Select the Schedules tab, and click on the New button to create a new schedule

   

3. In New Job Schedule window, provide the schedule name, and set the type and frequency for the schedule. As this schedule is used by the job that backs up the system databases, it will be set to run once per day at 12:00 AM. Optionally, set the start and end dates for the schedule. Click OK to save changes

   

4. Click OK button in Job Properties window to use created schedule. The job will run automatically on the specified schedule from now on

Next articles in this series:

• Ola Hallengren’s SQL Server Maintenance Solution – Database integrity check
• Ola Hallengren’s SQL Server Maintenance Solution – Index and statistics maintenance

Downloads

• MaintenanceSolution.sql

Useful links

• SQL Server Backup
• Tweaking the Defaults for Ola Hallengren’s Maintenance Scripts

• Author
• Recent Posts

Robert Seles

Robert is a SQL Server and IT enthusiast with many years of experience as a computer repair technician.

He has a great passion for new technologies, science, video games, and medieval history. He also likes reading, writing, and learning new languages.

Robert is currently working for ApexSQL LLC as Software Sales Engineer where he specializes database backups and indexes.

View all posts by Robert Seles

Latest posts by Robert Seles (see all)

• Multiple methods for scheduling a SQL Server backup automatically - July 17, 2018
• Ola Hallengren’s SQL Server Maintenance Solution – Index and statistics maintenance - January 23, 2018
• Ola Hallengren’s SQL Server Maintenance Solution – Database integrity check - January 10, 2018