One major piece that is missing from SQL Express is SQL Agent and the ability to schedule routine maintenance like database integrity checks, index/statistics maintenance, and backups. I prefer to run Ola Hallengren’s scripts on all of my production SQL instances due to their performance benefits, ease of portability and configuration, and logging options. These scripts are used around the world and use built in SQL objects to perform standard database maintenance. They are easy to configure and well tested. Luckily the same scripts can be used to maintain SQL Express instances with the use of the Windows Task Scheduler.
Before you get started I recommend creating three directories on your server. One named SQLBackups and one named SQLJobLogs. The first will house your database backups and the latter will contain logs for your maintenance jobs. The third directory I create is called SQLScripts and will contain the jobs/batch files for all of my maintenance tasks.
Now you need to go to http://ola.hallengren.com/ and download the latest version of the scripts. Open the script and you will need to modify three parameters. Change the @CreateJobs parameter to ‘N’ since SQL Express doesn’t support SQL Agent jobs anyway. Next update the @BackupDirectory and @OutputFileDirectory to point to the two directories you created earlier. To install simply execute the script. I prefer to create a database named DBAUtility to house the stored procedures and logging table but any database including master can be used.
At this point all of the framework is in place to perform your maintenance tasks. I usually follow the schedule of weekly index/statistic maintenance, followed by database integrity checks, and then full database backups. I also perform daily differential database backups.
Creating the “Jobs”
Since SQLExpress doesn’t have the ability to create SQL Agent Jobs we will be creating batch files instead. The batch files will be scheduled via Windows Scheduled Tasks.
In my SQLScripts folder I will create a batch file for each of my tasks. The nice thing about using these scripts is they can be suited to fit your maintenance needs. The examples below contain my normal routines but feel free to modify things to suit your needs.
I create a batch file named 1-IndexMaint-AllDB.bat. This file will perform index maintenance against all of my databases. It contains the following line:
sqlcmd -E -S SQLExpressInstanceName -d DBAUTILITY -Q “EXECUTE [dbo].[IndexOptimize] @Databases = ‘ALL_DATABASES’, @FragmentationLow = NULL, @FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’, @FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’, @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @UpdateStatistics = ‘ALL’, @OnlyModifiedStatistics = ‘Y’, @LogToTable = ‘Y’” -b -o C:\SQLJobLogs\1-IndexMaint-AllDBs.txt
sqlcmd is a command line utility that can be used to issue transact SQL statements. The -E flag is for windows authentication, -S is the instance name, -d is the database that houses the script, and -Q is my query.
In this example it is calling the IndexOptimize stored procedure that was created in my DBAUtility database. Each index is analyzed for fragmentation and then the specified maintenance is performed on it.If fragmentation is < 5% nothing is done, if it is > 5% and < 30% then a index reorganization is performed. If that is not possible on online rebuild is attempted. If that is not possible an offline rebuild is attempted. If fragmentation is > 30% the online/offline rebuilds are attempted. I also update modified statistics and log to my CommandLog table in DBAUtilitiy and my SQLJobLogs folder. All of these options and more are well documented on http://ola.hallengren.com/.
For my 2-IntegrityChecks-AllDBs.bat file I use:
sqlcmd -E -S SQLExpressInstanceName -d DBAUTILITY -Q “EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = ‘ALL_DATABASES’, @CheckCommands = ‘CHECKDB’, @LogToTable = ‘Y’” -b -o C:\SQLJobLogs\2-IntegrityChecks-AllDBs.txt
Full Database Backups
For my 3-FullBackups-AllDBs.bat file I use:
sqlcmd -E -S SQLExpressInstanceName -d DBAUTILITY -Q “EXECUTE [dbo].[DatabaseBackup] @Databases = ‘ALL_DATABASES’, @Directory = ‘C:\SQLBackups’, @BackupType = ‘FULL’, @CleanupTime = ‘312’, @Verify=’Y’, @CheckSum = ‘Y’, @LogToTable = ‘Y’” -b -o C:\SQLJobLogs\3-FullBackups-AllDBs.txt
This performs a full backup on every database to my SQLBackups folder. The backups are kept for 312 hours before they are removed. I also perform checksum verifications on the backups and log to my table and SQLJobLogs folder.
This takes care of my weekly tasks. In order to keep my logging table clean I create a 4-CleanLogTable.bat file that cleans up logs older than 45 days.
sqlcmd -E -S SQLExpressInstanceName -d DBAUTILITY -Q “DELETE FROM [dbo].[CommandLog] WHERE StartTime < DATEADD(dd,-45,GETDATE())” -b -o C:\SQLJobLogs\4-CleanUpLogTable.txt
You may also want to create a file to keep your SQLJobLogs cleaned up.
Differential Database Backups
I also create a 5-DiffBackups-AllDBs for my daily differential backups.
sqlcmd -E -S SQLExpressInstanceName -d DBAUTILITY -Q “EXECUTE [dbo].[DatabaseBackup] @Databases = ‘ALL_DATABASES’, @Directory = ‘C:\SQLBackups’, @BackupType = ‘DIFF’, @CleanupTime = ‘168’, @Verify=’Y’, @CheckSum = ‘Y’, @LogToTable = ‘Y’” -b -o C:\SQLJobLogs\6-DiffBackups-AllDBs.txt
Putting it All Together
In order to string things together II create two more batch files. One named WeeklyMaint.bat and one called DailyMaint.bat.
The last step is to add a Windows Scheduled Task that runs WeeklyMaint.bat once a week and DailyMaint.bat nightly.
As these run the results of the jobs will be logged in your CommandLog table and SQLJobLogs folder.
Copying Backups Off Server
I also recommended adding another step in the process to copy your backups to a 2nd location in case of a drive failure. I use a simple RobyCopy script for this.
%RoboCopyPath% %Source% %Dest% /mir /zb /r:30 /v /fp /log:%LogFOlder%\RoboCopyLog.txt
if errorlevel 3 echo OKCOPY + XTRA & goto end