I would like to rebuild many of our SQL scripts into Powershell to further integrate my department with the Windows Operations team and to open up a lot of other options.


1-preference over invoke-sqlcmd vs. just using powershell and SMO cmdlets?

Do you have a major preference over invoke-sqlcmd vs. just using powershell and SMO cmdlets?

I am looking at using invoke-sqlcmd and copying over old code but with updated error handling, versus rewriting them into pure Powershell scripts. I know there are some pitfalls with using invoke-sqlcmd but the ability to have my scripts already 80% written is a big driver to avoid rewriting them.

Would you recommend just reusing old scripts, or are there benefits I can't find which would justify rewriting them in powershell?

2- Best Method To "Move To The Next Step"

How would you recommend having powershell execute the next entry in the array/loop only after the prior step has completed, successfully?

Each step of the job has a 'quit on success', I then plan on having it execute each step 1 at a time using a Powershell array or loop with all the step names in it. If it fails at any step, break and display error. Some steps take a long time however, and I don't want the other steps to start before the prior finishes completely. There's probably some powershell logic I can use without having to query the job status tables.

3-Error Handling

In this case, would you use try-catch blocks in SQL and in Powershell, would you recommend doing it in just powershell? I'm trying to pass the console output of the invoke-sqlcmd but this might be somewhere the native powershell variables might be easier to work with.

Currently I'm running:

  invoke-sqlcmd 'exec msdb..sp_start_job @job_name = "Refresh", @step_name = "step1"' -Verbose

This returns:

VERBOSE: Job 'Refresh' started successfully.

Would I then verify I had no errors by taking that output, perhaps using something like Write-Verbose, and performing my error handling that way? It seems like there's a better way.

Thanks in advance.

Edit: I decided to just change the jobs into stored procs and execute those from Powershell using an array for now. if anyone has a better suggestion let me know.

  • Having a hard time understanding how the Windows Ops team will use these PowerShell scripts better than they can manage SQL Server Agent jobs, particularly if the PowerShell is just calling BACKUP commands and executing other T-SQL logic. What exactly have you gained? Is it really that hard for them to review SQL Server Agent jobs/history when something has gone wrong? – Aaron Bertrand Apr 1 '14 at 1:06
  • You're also going to have to re-invent a lot of things if you go this route. For example, SQL Server Agent already has the ability to start the next job at the conclusion of the previous job (last step = sp_start_job). Since sp_start_job is asynchronous, I don't know how you're going to know the previous job finished, without polling. – Aaron Bertrand Apr 1 '14 at 1:12
  • Hi Aaron, in this case these are 'auto restore' scripts which are used for a few different scenarios: developers can execute to refresh environments, and they are part of a 'automated back office build up and tear down' process which can create multiple back offices. Since there's a lot of other dependencies Powershell ends up being really useful for tying everything from the web front end to back end processes on the Windows side. I was thinking of redo-ing backups in Powershell as well so the backup, copy backup, and validate backups copied can all be in 1 scripting language. – Ali Razeghi Apr 1 '14 at 18:17
  • Aaron, thanks for the input on Polling. It looks like I'll have to do something similar, but I'm curious what the logic in SQL Server Agent is that it can continue to the next step. Perhaps we can recreate it in a simple .Net app. I'll dig around and if I find anything useful, I'll update the thread. – Ali Razeghi Apr 1 '14 at 18:18

Your Answer

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

Browse other questions tagged or ask your own question.