Run PS-Script after job is done on 3 instances - SQL Server

Question

I have backup jobs running on a server with 3 instances.

Link to script: https://ola.hallengren.com/sql-server-backup.html

The solution I found for the other 2 servers with only once instance was to create a job which has the backup scripts as steps via TSQL and then simply runs the PowerShell script.

Is there a way to do this on a multi-instance server?

Server is running SQL Server 2012

Answer 1

You have a few options on how you could handle this...

1. Create a Scheduled Task on that server that contains a PowerShell script. The first part of that script contains a workflow that would iterate over each instance and call the backup job on each instance. Use a foreach -parallel would allow all the jobs to start at one time. You would then need to script out a while loop to wait and see that all jobs have completed. Then the end of that PowerShell script just call the code you need to run.
2. Pick an instance and use it as a "central" location to actually run the backups against each instance. You could utilize linked servers to remotely call Ola' scripts. This would offer a bit more control and ease of management because you would know once the command finished the backup for that instance are complete. Your last step would just be your PowerShell script.
3. Leave each backup job on the given instance. Add a scheduled task that calls your PowerShell script, but at the start of that script check to make sure a recent backup has been done. If not, just stop the script. Schedule that task to run between an the average end time of the backup jobs until a given a few hours later. This would ensure at some point it will find that the backups on all instances are current, then execute your code.