SQL Agent Maintenance Plan Scripts

Owned by Steve Lynch (Deactivated)
Last updated: Feb 19, 2020
3 min read

Synergetic use a script to creates SQL Agent jobs to replace standard maintenance plans for initial server setup or migration. The maintenance solution script is based on scripts created by Ola Hallengren with the schedules and job names changed to meet Synergetic requirements. Changes were made to the maintenance procedures so if a new version is released, the functions and procedures should be able to be replaced. This makes the plans simple to create, portable during server migrations and standardised amongst sites.

If a server needs the maintenance plans then this script can be run to create the following SQL Agent jobs which emulates maintenance plans:-

  • Syn Database Backup (script) – Daily
  • Syn Transaction Log Backup (script) – 15m intervals
  • Syn Database Tune Up (script) – Weekly
  • Syn Database Integrity Check (script) – Weekly

The naming of the scripted jobs is slightly different to those previously created via SSMS Management > Maintenance Plans so it is easy to differentiate a site with the new scripted jobs or the old maintenance plans.

Important note: After initial setup, the system backups and maintenance scripts become the responsibility of the system administrator and ongoing monitoring and maintenance must be performed. Synergetic support does not cover maintenance or updates/fixes to the maintenance jobs and charges may apply for assistance with these. Please see Synergetic Systems Responsibilities for further details.

Maintenance Solution Source Script

The template script can be downloaded from the link below.

*Last updated 19/02/2020 with latest Ola Hallengren stored procedures as well as enabling backup compression and checksum defaults.

Replace the variables within the parameter section to suit the requirements:

/*******************************************************
CHANGE PARAMETERS
*******************************************************/

SELECT
@BackupDirectory = N'D:\SQLBackup', -- Specify the backup root directory.
@CleanupTime = 168 -- Time in hours, after which backup files are deleted. If no time is specified, then no backup files are deleted.

/*******************************************************/

Jobs Created by this script

Syn Database Backup (script) – Daily

Usage

Full backup of all system and user databases.

Command

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'ALL_DATABASES', @Directory = N'D:\SQLBackup', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 168, @CheckSum = 'Y', @LogToTable = 'Y'" -b

Schedule

Daily at 10:00pm

Notes

Unlike standard maintenance plans, the full backups are named differently and placed in a directory structure as follows – {BackupDirectory}\{ServerName}\{DatabaseName}\FULL\{BackupFile}

Eg. D:\SQLBackup\HPEL8100G\SynergyOne\FULL\ HPEL8100G_SynergyOne_FULL_20130305_220001.bak


Syn Transaction Log Backup (script) – 15m intervals

Usage

Regular transaction log backups of all user databases.

Command

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'D:\SQLBackup', @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 168, @CheckSum = 'Y', @LogToTable = 'Y'" -b

Schedule

Every 15 minutes

Notes

Unlike standard maintenance plans, the full backups are named differently and placed in a directory structure as follows – {BackupDirectory}\{ServerName}\{DatabaseName}\LOG\{BackupFile}

Eg. D:\SQLBackup\HPEL8100G\SynergyOne\LOG\ HPEL8100G_SynergyOne_LOG_20130305_171500.trn


Syn Database Tune Up (script) – Weekly

Usage

Optimise table indexes on all databases and clean up backup, job and command history.

Command

  1. sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[IndexOptimize] @Databases = 'ALL_DATABASES', @LogToTable = 'Y'" –b
  2. sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d msdb -Q "DECLARE @CleanupDate datetime SET @CleanupDate = DATEADD(dd,-30,GETDATE()) EXECUTE dbo.sp_delete_backuphistory @oldest_date = @CleanupDate" –b
  3. sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d msdb -Q "DECLARE @CleanupDate datetime SET @CleanupDate = DATEADD(dd,-30,GETDATE()) EXECUTE dbo.sp_purge_jobhistory @oldest_date = @CleanupDate" –b
  4. cmd /q /c "For /F "tokens=1 delims=" %v In ('ForFiles /P "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log" /m *_*_*_*.txt /d -30 2^>^&1') do if EXIST "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log"\%v echo del "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log"\%v& del "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log"\%v"
  5. sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "DELETE FROM [dbo].[CommandLog] WHERE DATEDIFF(dd,StartTime,GETDATE()) > 30" -b

Schedule

Sunday 2am

Notes

This is much faster than the standard maintenance plan index optimisation as it only re-indexes tables that actually require it.


Syn Database Integrity Check (script) – Weekly

Usage

Check for database corruptions by verifying tables and data pages.

Command

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'ALL_DATABASES', @LogToTable = 'Y'" -b

Schedule

Sunday 4am


https://ola.hallengren.com/

https://www.red-gate.com/simple-talk/sql/database-administration/automate-and-improve-your-database-maintenance-using-ola-hallengrens-free-script/

https://www.sqlserverblogforum.com/dba/how-to-use-configure-ola-hallengren-sql-server-maintenance-script/

https://www.sqlshack.com/ola-hallengrens-sql-server-maintenance-solution-installation-sql-server-backup-solution/

https://www.brentozar.com/archive/2018/07/ola-hallengrens-scripts-keep-getting-better/