Using PowerShell, deploy the ola hallegren Maintenance Solution Script. When we run the script below, we receive the following results.
-> Jobs in SQL will be created.
-> SQL Job Schedules will be created as well.
-> Some stored procedures will also get created ( SP’s are from Brentozar site )
Above all, we must do some manual work in order to be produced.
Requirements: We’ll need Sysadmin access to all of the machines where we’ll be running our PS script.
Step 1 : Ready a script to create DBA database for storing helpful stored procedures and maintenance logging Step 2 : Download and save the latest scripts to Create the below stored procedures in the DBA database which are very useful - Sp_whoisactive: https://github.com/amachanic/sp_whoisactive/ - Sp_Blitz: http://www.brentozar.com/blitz/ - Sp_BlitzIndex: http://www.brentozar.com/blitzindex/ - Sp_BlitzCache: http://www.brentozar.com/blitzcache/ - Sp_AskBrent: http://www.brentozar.com/askbrent/ Step 3 : Download Latest script to Create Ola Hallengren Maintenance procedures: https://ola.hallengren.com/ Step 4 : Get ready with the scripts to Create the maintenance jobs for backups, DBCC CheckDB, Index Maintenance and maintenance cleanup Step 5 : Schedule the maintenance jobs created in step 2 Step 6 : Create CSV file and enter your Instance Names on each line, save the file and close.
Downloaded all the latest scripts and kept in my local c:\temp\ola folder
Please notice the snippet below, which shows how all the scripts are organized into folders:
If the Instance Names are entered into the CSV file as described in Step 6, we can begin deploying the ola hallegren Maintenance Solution script by running the PowerShell script ‘Deploy-SQLMaintenance.ps1’.
I’ve deployed this to a single instance in my system, and the result is as follows:
All stored procedures are deployed to the MASTER Database in the instance (you can alter the database name to deploy the SP’s depending on your setup).
I ran Maintenance jobs and checked the ‘command Log’ Table by querying this table, we can see all of the job details such as job start and end times, etc.
If you wish to update the Job Schedules, go to the Schedules Folder and look for three files with the titles prod, dev, and test environment. These files include schedule information for all three environments.
You can access the file and make the necessary adjustments to the job schedules to suit your needs. Once you’ve finished making changes, click Save and close the file :
You can also alter the Database Name by opening the file ” Database Creation “, where everything will be saved.
Please see the following excerpt to make changes to the Job creation file:
You may download the whole script from the following Google Drive link: