Applies to
All ApexSQL console applications with Command line interfaces (CLI)
Summary
This article describes how to schedule ApexSQL tool tasks to run unattended, at a specific time/date, etc., using four different ways of scheduling.
Description
In the following examples, the most common and easiest ways of scheduling ApexSQL tool tasks will be explained. Windows authentication is used in all cases. To see alternative ways of handling login credentials, check out the 4 ways of handling database/login credentials during automated execution via the CLI article.
Option 1 – Scheduling via Task Scheduler
Task Scheduler is a Windows based application that enables users to schedule tasks to run automatically at a specific time/date or when a specific event occurs. It also provides a way of organizing tasks by creating folders, so that desired tasks are organized properly:
Learn more about Task Scheduler from here.
When it comes to scheduling ApexSQL tools, a PowerShell script or a batch file can be created containing ApexSQL tool CLI switches and used as a starting action in Task Scheduler. Learn more about How to automate and schedule CLI execution with Task Scheduler.
Option 2 – Scheduling via SQL Server Job
A SQL Server Job represents a variety of operations that can be scheduled as steps using SQL Server Agent. Various types of operations can be scheduled such as: Operation system (executable program or operating system command), PowerShell scripts, Transact-SQL scripts (T-SQL), etc.:
Learn more about creating SQL Server Job from here.
Similar to the previously described Task Scheduler, ApexSQL tools can be scheduled with a SQL Server Job by creating a batch file or PowerShell script executing an ApexSQL console application via its Command Line Interface. This job can be loaded and created as a step in SQL Server Management Studio. To learn more about How to automate and schedule CLI execution with SQL Server Job.
Option 3 – Scheduling via PowerShell project
If PowerShell is your preferred choice of automation, then check out the following example in which ApexSQL Diff is used to schedule a job comparing database schemas, creating HTML report of changes, and spooling the processes’ results to a text file.
There are two ways to perform this kind of scheduling of ApexSQL tools via PowerShell project. In both cases the created task in the PowerShell project contains switch variables that specify the task frequency, its name, and path:
1. Using a project file – where everything is set via the tool’s GUI and saved as a project file that will be used in the PowerShell project. In this example, ApexSQL Diff will be scheduled to run each day at 2 AM. The project switch and value is highlighted in green:
| Schtasks.exe | #allows creating, deleting, changing, etc. scheduled tasks. If it’s run without arguments, it will show the status and next time when a task will be run, for each created task |
| /Create | #creates a task |
| /SC DAILY | #task frequency |
| /ST 02:00 | #task start time |
| /ET 03:00 | #task end time |
| /TN CompareAndReport | #task name |
| /F | #switch that overwrites a task, if already exists |
| /TR | #a path for a task that will be run at the scheduled time |
| ApexSQLDiff | #application’s path |
| /pr:”MyProject.axds” | #a path to the saved project file |
| /ot:html | #output type |
| /on:”SchemaReport.html” | #output type path and name |
| /v | #prints all messages in console |
| /f | #overwrites existing files |
| /out:”Output.txt” | #redirects console output to specified file |
| @echo ApexSQL Diff return code is %errorlevel% >> /out: ”Output.txt” | #return code upon finished application execution outputted to specified file |
2. Using a batch file – where everything is set via the tool’s GUI and saved as a batch filethat will be used in the PowerShell project. Comparing to a project file, a batch file will contain ApexSQL Diff location and all necessary CLI switches, so the only thing that will be called is the batch file itself. In this example, ApexSQL Diff will be scheduled to run hourly each day from 1 AM to 6 AM. (The batch file is highlighted in green):
| Schtasks.exe | #allows creating, deleting, changing, etc. scheduled tasks. If it’s run without arguments, it will show the status and next time when a task will be run, for each created task |
| /Create | #creates a task |
| /SC HOURLY | #task frequency |
| /ST 01:00 | #task start time |
| /ET 06:00 | #task end time |
| /TN CompareReport | #task name |
| /F | #switch that overwrites a task, if already exists |
| /TR | #a path for a task that will be run at the scheduled time |
| SchemaComp.bat | #batch file path |
Learn more about scheduling tasks via PowerShell project from here.
Option 4 – Scheduling multiple ApexSQL tools via a PowerShell project to run consecutively
When it comes to scheduling multiple tasks for execution, a slightly different approach will be used. In the following example, ApexSQL Diff and ApexSQL Data Diff will be used to schedule the synchronization of SQL database schemas and data respectfully, creating an Excel export of the difference and outputting the application execution results into a text file.
As in previous scheduling examples, an ApexSQL project file or a batch file can be used when scheduling multiple ApexSQL tools is required. Also when using a batch file there is less code involved when creating the PowerShell project.
1. Using a project file – project files are created and saved from GUI for both ApexSQL tools and will be used along with other CLI switch values to schedule tools execution each day from 9AM and 8h between each start of the task for the next 365 days:
| Register-ScheduledTask ‘SchemaDataSync’ | #registers a scheduled task with its name |
| -InputObject | #inputs to this cmdlet |
| (New-ScheduledTask | #creates a scheduled task instance |
| -Action ( | #specifies an array of work items for a task to run |
| (New-ScheduledTaskAction | #creates a scheduled task action for the first application in line |
| -Execute ‘”ApexSQLDiff”‘ | #calls ApexSQL Diff application from the installation location to synchronize schemas between a source and a destination database |
| -Argument ‘/pr:”SchemaSync.axds” /sync /v /f /ot:excel /on:”DataExport.xml” /out:”outApexSQLDiff.txt”‘), |
#the argument command contains all needed ApexSQL Diff CLI switches. Learn more about it from here |
| (New-ScheduledTaskAction | #creates a scheduled task action for the second application in line |
| -Execute ‘”ApexSQLDataDiff”‘ | #calls ApexSQL Data Diff application from the installation location to synchronize data between a source and a destination database |
| -Argument ‘/pr:”DataSync.axdd /sync /v /f /ot:excel /on:”SchemaExport.xml” /out: ”outApexSQLDataDiff.txt”‘)) |
#the argument command contains all needed ApexSQL Data Diff CLI switches. Learn more about it from here |
| –Trigger | #specifies an array of one or more trigger objects that cause a scheduled task to start |
| (New-ScheduledTaskTrigger | #creates a scheduled task trigger object |
| -Once -At 9AM | #trigger starts a task once at a specified time with the –At parameter |
| -RepetitionInterval (New-TimeSpan -Hours 8) | #amount of time between each start of a task |
| -RepetitionDuration (New-TimeSpan -Days 365))) | #amount of time for how long the repetition pattern will be repeated after the task is started |
2. Using a batch file – batch files are created and saved from the GUI for both tools and will be used to schedule execution each day from 6AM and 1h between each start of the task for the next 180 days:
| Register-ScheduledTask ‘SchemaDataSync’ | #registers a scheduled task with its name |
| -InputObject | #inputs to this cmdlet |
| (New-ScheduledTask | #creates a scheduled task instance |
| -Action ( | #specifies an array of work items for a task to run |
| (New-ScheduledTaskAction | #creates a scheduled task action for the first application in line |
| -Execute ‘”SchemaSync.bat”‘ | #calls ApexSQL Diff batch file that contains all needed CLI switches |
| (New-ScheduledTaskAction | #creates a scheduled task action for the second application in line |
| -Execute ‘”DataSync.bat”‘ | #calls ApexSQL Data Diff batch file that contains all needed CLI switches |
| –Trigger | #specifies an array of one or more trigger objects that cause a scheduled task to start |
| (New-ScheduledTaskTrigger | #creates a scheduled task trigger object |
| -Once -At 6AM | #trigger starts a task once at a specified time with the –At parameter |
| -RepetitionInterval (New-TimeSpan -Hours 1) | #amount of time between each start of a task |
| -RepetitionDuration (New-TimeSpan -Days 180))) | #amount of time for how long the repetition pattern will be repeated after the task is started |
|
Quick tip: Each tool that’s called by the -Execute parameter and all tool’s CLI switches after the -Argument parameter need to be under single quotation marks. |
FAQs
Q: Do I have to reference the application path when using a batch file?
A: No. The application path is already contained in a batch file and a batch file is the only thing that needs to be called to execute ApexSQL application.
Q: Can I use a project file along with a batch file?
A: Yes. A project file can be created in the Save as batch window and used in a created batch file. Learn more from here.
