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

| improve this question | | | | |
  • I don't understand. Each instance of SQL Server will have it's own SQL Agent, just create the backup scripts in each instance. If you are running the jobs from Task Manager then just add multiple steps, one for each instance. – Jonathan Fite Oct 28 '15 at 15:49
  • running the job in SQL managment studio. what you are talking about is my question, where can i do this easily? why task manager? is has to be scheduled – Bengelnatz Oct 28 '15 at 19:19
  • 1
    You have the jobs visible in SQL Agent? You just need to go to each one and add a schedule to the job so it will run on it's own. Ola's scripts may create the jobs but it doesn't create schedules for them. – Jonathan Fite Oct 28 '15 at 19:21
  • 1
    yeah but I need all the jobs done on 3 instances and THEN run a powershellscript. – Bengelnatz Oct 28 '15 at 21:32
  • so it's a dependency issue? The easiest method is to pick one server and have it launch the job on the other servers and then run the powershell script when they are all done. – Jonathan Fite Oct 29 '15 at 22:18

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.
| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.