SQL Agent Maintenance Plan Scripts

Modified on Mon, 9 Mar at 1:33 AM

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 Files section

*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	sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[IndexOptimize] @Databases = 'ALL_DATABASES', @LogToTable = 'Y'" –b 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 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 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" 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

Reference Links

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/

SQL Agent Maintenance Plan Scripts

Maintenance Solution Source Script

Jobs Created by this script

Syn Database Backup (script) – Daily

Syn Transaction Log Backup (script) – 15m intervals

Syn Database Tune Up (script) – Weekly

Syn Database Integrity Check (script) – Weekly

Reference Links