Ola Hallengren Script

• shirish_padekar

  SSC Eights!

  Points: 830

  More actions

  July 29, 2015 at 9:53 am

  #306199

  We are trying to use SQL Server Maintenance Solution from https://ola.hallengren.com/

  I am a asp.net programmer and have basic knowledge of sql server. We have SQL Server 2008 R2 express edition on server and hence can't use SQL Server Agent Jobs to schedule this script. I have created 3 .bat files with below commands in them:

  Weekly.bat:

  sqlcmd -E -S .\SQLEXPRESS -d Testing -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @databases = 'SYSTEM_DATABASES', @LogToTable = 'Y'" -b -o C:\Log\DatabaseIntegrityCheck_System.txt

  sqlcmd -E -S .\SQLEXPRESS -d Testing -Q "EXECUTE [dbo].[DatabaseBackup] @databases = 'SYSTEM_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL', @verify = 'Y', @CleanupTime = NULL, @checksum = 'Y', @LogToTable = 'Y'" -b -o C:\Log\DatabaseBackupFull_System.txt

  sqlcmd -E -S .\SQLEXPRESS -d Testing -Q "EXECUTE [dbo].[IndexOptimize] @databases = 'USER_DATABASES', @LogToTable = 'Y'" -b -o C:\Log\IndexOptimize_User.txt

  sqlcmd -E -S .\SQLEXPRESS -d Testing -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @databases = 'USER_DATABASES', @LogToTable = 'Y'" -b -o C:\Log\DatabaseIntegrityCheck_User.txt

  sqlcmd -E -S .\SQLEXPRESS -d Testing -Q "EXECUTE [dbo].[DatabaseBackup] @databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL', @verify = 'Y', @CleanupTime = NULL, @checksum = 'Y', @LogToTable = 'Y'" -b -o C:\Log\DatabaseBackupFull_User.txt

  sqlcmd -E -S .\SQLEXPRESS -d Testing -Q "DELETE FROM [dbo].[CommandLog] WHERE StartTime < DATEADD(dd,-30,GETDATE())" -b

  sqlcmd -E -S .\SQLEXPRESS -d msdb -Q "DECLARE @CleanupDate datetime SET @CleanupDate = DATEADD(dd,-30,GETDATE()) EXECUTE dbo.sp_delete_backuphistory @oldest_date = @CleanupDate" -b

  sqlcmd -E -S .\SQLEXPRESS -d msdb -Q "DECLARE @CleanupDate datetime SET @CleanupDate = DATEADD(dd,-30,GETDATE()) EXECUTE dbo.sp_purge_jobhistory @oldest_date = @CleanupDate" -b

  Daily.bat:

  sqlcmd -E -S .\SQLEXPRESS -d Testing -Q "EXECUTE [dbo].[DatabaseBackup] @databases = 'SYSTEM_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL', @verify = 'Y', @CleanupTime = NULL, @checksum = 'Y', @LogToTable = 'Y'" -b -o C:\Log\DatabaseBackupFull_System.txt

  sqlcmd -E -S .\SQLEXPRESS -d Testing -Q "EXECUTE [dbo].[DatabaseBackup] @databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'DIFF', @verify = 'Y', @CleanupTime = NULL, @checksum = 'Y', @LogToTable = 'Y'" -b -o C:\Log\DatabaseBackupDiff_User.txt

  Hourly.bat:

  sqlcmd -E -S .\SQLEXPRESS -d Testing -Q "EXECUTE [dbo].[DatabaseBackup] @databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'LOG', @verify = 'Y', @CleanupTime = NULL, @checksum = 'Y', @LogToTable = 'Y'" -b -o C:\Log\DatabaseBackupLog_User.txt

  I will be executing these 3 .bat files using windows task scheduler.

  1. I would like to know is this the correct way to implement this on sql server express edition?

  2. Also, is the sequence of commands is proper in above .bat files?

  3. And, I tried to pass only default parameters to each command, are these settings correct?

  4. Also, main issue I am facing with above is: I tried to pass output parameter to above commands but output file names are not changing each time script is executed, instead existing files were replaced. Do I need to have SQL Server Agent Jobs for this? Whats the solution for express edition?