Routine Maintenance Setup from SQL Scripts

If I do the same thing more than a few times I look for ways to automate them. Yes, I fully admit I am lazy. (Don’t tell my wife I said that). Let me clarify. If I can figure out a way to make my job and life easier I am all over it. This is a post is about one of the things I have taken steps towards automating, not fully mind you but it is better than manually creating agent jobs one at a time.

This is one of the steps I have created as part of our Standards for SQL Server Installations. After SQL has been installed we run this process as part of our server configuration and is used to install a few stored procedures and setup routine maintenance tasks. If these were done manually could take an average of 5 or more minutes per task. Now that may not seem like a lot of time but let us image you have 3 servers to setup and you have the following 7 maintenance jobs to create for each server. System database backup, User database backup, User database transaction log backup, Backup maintenance (removing old backup files), index defragging (as needed), database integrity checks, and maintenance history cleanup. That is easy math right? 5×7=35. 35 minutes not counting any interruptions you might have while creating the jobs. Now, compare that with, for argument sake 30 seconds. It will actually take less to run but we can add in the time to copy the files to the server, open the command prompt and call the file to start the install J so 35 x 3 = 105. That is more than an hour and a half. I don’t know about you but I can find a lot more productive things to do in an hour and a half.

What We Are Setting Up

If you look at the list the files are mostly self-explanatory. These may be named a little funky or different than you name or will name your files but this is how I do it, it works for me and that is all that matters. Moving on, steps 01 through 04 are core functionality. We are working with temp DB adding and moving data files to the correct drive, in our case “T:\”. We then enable database mail, create operators, and server alerts. Step 5 is a daily script that has some basic home grown monitoring information like service SQL version and pack level, when the databases were last backed up, drive space, system logins and permission level, and any error log information. Step 6 monitors failed login attempts and sends an email at certain intervals that can be adjusted. Step 7 is a stored procedure is database integrity checks. Step 12 disables the SA account. Step 21 is a script revoking permissions on some extended stored procedures and removes connect from the Guest account. Step 30 is my personal script that creates a stored procedure or two and sends that information to a central database I use to track my project databases.

The Meat Of The Process

If you were following along closely I mentioned all the scripts in the image except one. CreateSQLJobs.sql this script is the workhorse of the entire process. This one script calls all the others as the example shows. Want a new script added to the run? Create it, drop it in the correct folder add it to this script and boom! You are done. Want to remove on for this run? Just comment out the script you don’t want run. It is as simple as that. There are probably many different ways to accomplish this same thing, but this works for me.

Kicking Off The Install

Now, how do we call the CreateSQLJob script?  Fortunately that is also very simple. (anyone catching onto the theme here?) Just open up a command prompt, navigate to the folder root and execute the following command.


I created this process to make the mundane work of manually creating routine maintenance jobs easier. If you like dragging and dropping sub tasks onto a design window and creating jobs manually that is fine by me. If you want to be more efficient when setting up and configuring SQL installs and you want to work smarter not harder then give this a try. Hopefully this will make your next install that much quicker.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s