Ola Hallengren’s SQL Server Maintenance Solution & CMD Syntax

ola-hallengren

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?

Best Answer

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

Related Solutions

Sql-server – How to implement Ola Hallengren backup plan on the server for some databases nightly full backups and for other weekly full backup only

No you don't need to install the Ola's solution again since the initially installed commandexecute.sql and databasebackup.sql would have been saved on master DB as SP.

You just need to create a new job , as one created early but this time for 5 left over DB;s and schedule it weekly per you're timings.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',-- Name of those 5 DB's would come here
@Directory = 'C:\Backup',-- You're backup path
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y'

enter image description here You can use above script while creating the T-SQL job and schedule it accordingly.

More details on how to do above refer to schedule job

Also, just to make sure both, initial backup and this newly created backup job not to run on same time you can put a condition or per the analysis when you're daily backup job completes, you can schedule this new weekly/nightly backup job for remaining 5 DB's

Difference in Backup File Size

I don't think you're doing what you want to do. It looks like you're striping your backups across two paths, which means your backup is evenly split between the two locations.

I suspect that your actual backup size is not 3.5MB, but rather 7MB. The difference in size is most likely due to the local storage being quicker.

Per Ola Hallengren's documentation:

If you specify multiple directories, then the backup files are striped evenly across the directories.

I would strongly suggest modifying this script to only write to one path, and then use an automated method to copy this backup set to another location.

Best Answer

Related Solutions

Sql-server – How to implement Ola Hallengren backup plan on the server for some databases nightly full backups and for other weekly full backup only

Difference in Backup File Size

Related Question