Ola Hallengren's SQL Server Maintenance Solution & CMD Syntax

Question

I'm using the free version of SQL Server Management Studio so I have to use CMD to call the SQL scripts. The official page gives an example of the CMD syntax used to call the "DatabaseBackup" script, as follows:

Create cmd files to execute the stored procedures; for example: sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL'" -b -o C:\Log\DatabaseBackup.txt

I got that to work, but I can't get it to work for the "DatabaseIntegrityCheck" or the "IndexOptimize" scripts.

I guess I don't know the syntax. I tried using the example code and just replacing the script name, but that didn't work.

Can anyone help me?

Answer 1

How to automate SQL Server Express database maintenance using Windows Task Scheduler and Ola Hallengren’s maintenance solution has numerous examples of using SQLCMD to run Ola's scripts.

For example:

Database integrity check

sqlcmd -E -S .\SQLEXPRESS -d Tools -Q "EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'USER_DATABASES', @LogToTable = 'Y';" -b -o D:\OLA\Logs\DatabaseIntegrityCheckUserDatabases.txt

Index optimize

sqlcmd -E -S .\SQLEXPRESS -d Tools -Q "EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @LogToTable = 'Y';" -b -o D:\OLA\Logs\IndexOptimizeUserDatabases.txt