Ola Hallengren Maintenance part 1

Many of us are using Ola Hallengren’s maintenance solution.

This post will assist you in configuring this solution when you add it to your SQL Servers. Jobs are created with this solution. You download it at the link above.

• Backup Jobs for System and User databases
• Database Integrity jobs for System and User Databases
• Cleanup jobs for Command Log and Output Files

I recommended creating a database to use for this solution or even installing it into an existing DBA function database. I usually create a DBA database and use it for this purpose and others as well. With this new database, you configure the Database in the header of the maintenance solution SQL file, whether to create jobs, retention time and backup directory for the jobs.

Parameters to Fill Out

Pay close attention to the highlighted lines:
The database name you created is put in the USE statement and the following items are created in that database:

• CommandLog table
• Stored Procedures that are in the solution

I use the DBA database here, which keeps it out of System databases and in an isolated place.

The @CreateJobs variable specifies whether or not the create the jobs. If you already have it installed, turning that off is easy, you just change it to an ‘N’ and it will merely update the code so that you have the latest version.

Next is @BackupDirectory which is where you specify the database backup location you want to use for the backups. If you do not specify this one, then the default is used. The default location is in the Database Settings tab of the Server Properties in SSMS.

Next is @CleanupTime and it is specified in hours. If you want to clean up the backup files after 1 week, you specify 168 as the hours for cleanup time. This number is put in all the Backup jobs that are created.

Last but not least, is the @OutputFileDirectory which specifies the folder for the output files that are on each step in the Ola jobs. If not specified, it will default to the LOG directory specified in the startup of SQL Server. The SQL Server Errorlogs are kept in that folder. I usually leave the parameter to be NULL to leave the output files there.

The @LogToTable parameter is configured as ‘Y’ because this is going to be put in the jobs as well to ensure that the work that is done in this solution is logged in the dbo.CommandLog table in the specified database.

In the code block below, the data is filled out so you can see what it looks like and the jobs get created with these settings as their defaults. The stored procedures are created in the specified database as well.

Conclusion

It is important to remember that this solution creates jobs that have a structure and at https:.//ola.hallengren.com the documentation has a full complement of examples of how to configure these jobs with parameters. The items that you need to ensure are configured include @LogToTable = ‘Y’ and @Compress = ‘Y’ so that you get it logged to the table and the database backups are compressed. If you omit the @Compress parameter then it will rely on the server setting to compress backups by default. Never rely on a setting that can be changed with or without your knowledge.

This part was not meant to be 100% comprehensive, but to ensure that you have information of how to install the jobs and solution, other parts are going to go into more depth so that you can see how to use it in the real world.