page contents

About the Post

Author Information

Update to an earlier article… Ola Recommendations for his script

Hey everyone, sorry for the delay in writing new content, but a lot of stuff has been going on for me. For the meantime, I will just say that the change of everyday life and the challenges (or opportunities depending on your point of view) that it will present you as we do our best to get through it. Sometimes we are not prepared for them and it may overwhelm us at first.

With that being said, it is good to be back and I am excited to provide an update about this great script.

Today, I want to do 2 things… I would like to say thank you to Ola Hallengren for reaching out to me shortly after I wrote this article. He was very pleasant and polite as he asked a very simple question that started our dialogue:

Could I just ask one thing. Why is it that you prefer not to verify the backup files?

 Once he said that I kind of already knew where he was going and over the course of a few emails he got me back on track. Which now leads me to my second point and I am going to correct that earlier information and share with you what I learned. 🙂

First and foremost, it is highly recommended that you do a checksum on your backup as pointed out here. It is also recommended that you do a verify as well and that is what I am going to correct in this article. With that being said I am going to create a do over with this topic and will highlight the new recommendations.

As with the same with the last time, I want to talk a little bit more about configuring his script in hopes that you don’t just download it and click execute without making some changes.

Before even think about executing Ola’s maintenance script, I like to configure SQL server to turn on backup compression by default. This step is done to save disk space and I also turn on the advanced options of sp_configure: (Steps below)

— Change backup compression to be on

sp_configure ‘backup compression default’, 1 reconfigure

–Show advanced options 

EXEC sp_configure ‘show advanced option’, ‘1’

RECONFIGURE

GO 

— Check settings

sp_configure

It will return these results (backup compression default) when you are done: (1 is on and 0 is off)

Before you begin modifying Ola’s scripts, I would suggest that you the SQL Server backup and Frequently Asked Questions sections. It will help you determine what to configure for your business needs. It is important to point out that the recommended order of the jobs is to do the index maintenance, then integrity check, and finally the backups. (I will point these out again)

So right off the bat, let’s look at the first area where we need to change in the script. The location of the backups and here is the default location: (Click on the image for a larger view)

I changed mine to reflect where I wanted to back up to a Z drive and you will need to ensure the modified path exists: (Click on the image for a larger view)

Then the next thing, I looked for was the retention period of backups and make sure that you meet your requirements. I noticed this comment in the SQL Server backup section “Specify the time, in hours, after which the backup files are deleted. If no time is specified, then no backup files are deleted. Backup files are deleted after each database is backed up and verified. Backup files are deleted only if the backup and verification of the database were successful.

Please note that the stored procedure keeps the backups for the number of hours in the @CleanupTime parameter, regardless of the @Verify parameter. The only thing to note here is that if a backup or verify (if selected) fails, then no backup files are deleted.

Here are the default settings: (Just search for checksum)

These settings are left alone and no changes are needed. (Different from original post)

The last thing I do is order the steps. Stated earlier, the recommended order of the jobs is to do the index maintenance, then integrity check, and finally the backups.

The default settings are seen here:

To change them follow these steps:

  • Search (Control –F) for DatabaseBackup – SYSTEM_DATABASES – FULL


  • Label the Index Job step 1
  • Label your Integrity checks as the next 2 steps
  • Label the other jobs accordingly

When you are done following his recommendations then it should look like this:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

NOTE: The above 3 steps are done to help you configure the scheduling easier in SQL Agent jobs.

Please note that Ola makes these recommendations for frequency of running the jobs:

User databases:

  • Full backup one day per week
  • Differential backup all other days of the week
  • Transaction log backup every hour
  • Integrity check one day per week
  • Index maintenance one day per week

System databases:

  • Full backup every day
  • Integrity check one day a week

Cleanup:

  • sp_delete_backuphistory one day a week
  • sp_purge_jobhistory one day a week
  • CommandLog Cleanup one day a week
  • Output File Cleanup one day a week

When you execute the script, it will create the jobs and as you will see later on, the numbering did in fact help us when we schedule the jobs, since it will be in the correct order:

Please note that all of these jobs are configured with no schedules, so you will need to create them. Here is how the jobs look:

The last step is to configure the jobs to run with either his recommendations or modifying those recommendations to fit your needs. I have swapped out all of my old scripts in favor of his scripts and I have been really impressed with the results.

The last thing to do is configure your backup retention for your current polices. Here is the default setting:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q “EXECUTE [dbo].[DatabaseBackup] @Databases = ‘SYSTEM_DATABASES’, @Directory = N’Z:\Backups’, @BackupType = ‘FULL’, @Verify = ‘Y’, @CleanupTime = 24, @CheckSum = ‘Y’, @LogToTable = ‘Y'” -b

For a one week retention period, you will need to use 168, which is 7 *24.

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q “EXECUTE [dbo].[DatabaseBackup] @Databases = ‘SYSTEM_DATABASES’, @Directory = N’Z:\Backups’, @BackupType = ‘FULL’, @Verify = ‘Y’, @CleanupTime = 168, @CheckSum = ‘Y’, @LogToTable = ‘Y'” -b

That is all you need to do here to keep you 1 week worth of data and now you also get a verify of your backup as well. The mistake that I made earlier was reading this information and coming away with a different understanding: (Click on the image for a larger view)

I thought that meant that as long as you are not verifying the backup then it will not remove files, since it needed to do both a backup and verification. It was much simpler than that… all you need to do was modify the job and then set the cleanup time as desired. Lucky for me, Ola caught this and shared this bit of wisdom.

In general you should modify/add parameters in the jobs. You should not change the default values in the stored procedures.Ola Hallengren

Tags: , , , , ,

One Response to “Update to an earlier article… Ola Recommendations for his script”

  1. Darren Stanger #

    Scott, these are very useful suggestions. Thanks for sharing and maintaining after feedback from Ola.

    One thing that I found useful when renaming the Jobs with numbers was to put a 0 (zero) in front of the numbers for jobs 1 – 9. Then it listed the 10 – Output File Cleanup and 11 – CommandLog Cleanup jobs at the bottom of the list of jobs.

    Thanks again!

    October 1, 2012 at 8:58 AM
Copy Protected by Chetan's WP-Copyprotect.