About John Sansom

I’m a Microsoft Certified Master(MCM) of SQL Server. I’ve been working with database technology in a variety of flavors for over fifteen years. I absolutely love what I do and genuinely feel privileged to be a part of our tremendous technology community. Got a question about SQL Server or being a DBA? Ask me!


Comments

  1. Thanks for putting this out there. I wanted to do something similar and was able to use your script and get everything scripted out in about 5 minutes. I guess I can go home now.

    • Great work Mike! Working smarter not harder in action there. It’s part of the PowerShell magic.

      I don’t know about clocking off early for the day but I would say you’ve certainly earned yourself a beer.

      Thanks for your comments sir.

  2. Hey John, 

    Another enlightening article once again mate. Good stuff!Powershell is something we are seriously underutilising in our shop. Do you have any useful links to hand where I can start learning and produce magic with?

    • Hey Samuel, thanks. I’m glad you enjoyed it.

      It’s interesting because right now I don’t actually have a favourite go to resource for PowerShell. Instead I seem to encounter snippets of useful content and knowledge spread across various online sources. I am however currently reading the book “Learn Windows PowerShell in a Month of Lunches” which I’m particularly enjoying.

      Sounds like I should be putting together a list of PowerShell resources on the blog. As you discover resources that you find particularly good do let me know!

      Thanks for your comments!

  3. SoftwareMark says:

    Your custom script works beautifully. Just tested on multiple SQL Servers.

    Thanks for sharing!

  4. mbourgon says:

    Here’s a small modification of your code to allow it to script out each job to a separate file, and deal with non-windows characters.  

    • Hi thanks for your suggestions!

      I hope you don’t mind but I removed the source code you provided because it does not format well in post comments. By all means feel free to post a reference to a formatted source.

  5. thank you for this script. i just used it to capture 4 of my servers jobs. I have them usually in individual scripts, but have missed some. This ensures that I have external copies of the code for my jobs and i appreciate it.

  6. Khellian says:

    Thanks for a great script. We are building out a multi-tiered environment based the setup of one server where there are over a dozen agent jobs. This saved me loads of time. 

  7. Great Script!! Thanks for Sharing John!!

  8. Now that you have the jobs scripted out how do you load the jobs with powershell to a SQL Server?

    • Jesse, Good question. The reasoning for this script was to create a backup of SQL Server Agent Jobs. In my use case the scripts would simply be run manually on the instance where they were needed.

      A PowerShell script could certainly be created in order to automate a restore process.

      • Hi John,
        I am just beginner to powershell and i need your help to automate one task.

        I have 90 Backend servers and still we are in testing phase. You know as of now we are still doing deployment. After deployment completes, i need to execute SQL script which is create JOB script in all 90 servers. As of now we are doing this manually by connecting to every server instance to execute the createbackupjob.sql script.

        I know the power shell script to create a JOB in a single server at a time. But i want my Power shell script whcih can execute this CreatebackupJob.Sql script in all 90 backend servers by accepting some serverlist.txt whichin contains all server names.

        Please suggest me how can we automate to run .sql script in all 90 servers.
        Thank you.
        Shafi

        • Have you considered using SQLCMD for this requirement instead? You could just create a .bat file that calls 90 SQLCMD statements.

          Alternatively, you could just call a SQLCMD statement from within PowerShell using Invoke-Sqlcmd.

          • When I have to deploy the same script on more servers, I use SSMS. Put all the servers in a group and just richt click on the group and choose New query.
            Sorry, nothing to do with Powershell ;)

            • Absolutely using Central Management Server is a great way to run an identical T-SQL script against multiple instances, an excellent suggestion Robbert.

              Unfortunately this method will not help Shafi, as he wants to run the distinct T-SQL scripts that are produced for each instance(via the PowerShell script in the post) against arbitrarily defined target instances.

  9. Here is a powershell suggested topic for you:

    Create a powershell script/function that will grab the latest version of a stored procedure in team foundation server and deploy it to a server/database. Would be incredibly useful for dba’s

  10. Hi John,

    I am beginner to power shell and I got some brief idea about the same through your post. However, I would like to know more about power shell scripting that is how to execute power shell scripts, where to execute and how to enable power shell. Can you please share more details about the same so that I can give a try to check out the functionality.

    Thanks and regards,
    Vijay.

  11. I would love to see the suggested mod to get the script to output all jobs as individual files.
    Thanks
    -S

  12. Hi John,

    The article which you have provided is very much helpful for a beginner like me. Now I got a clear picture about Powershell.

    Thank you very much for your support.

  13. One of the valuable solutions for scripting out sql jobs in sql server 2008R2
    without installing additional garbage.
    Thank you

    • Michael – Delighted to hear you found the script useful. PowerShell is a brilliant tool for automation and working with SQL Server.

      This particularly script came in handy as part of a larger DR requirement for my own use. What’s your use case and how are you finding PowerShell?

  14. Would also love to see the tweak to script out the jobs individually per server. Would make doing DIFF work against environments much easier.

    • Daivd – The script has been updated to include the source code required to perform this operation. Simply swap in (comment in, and comment out) the relevant section of code.

  15. John,
    I am trying to restore the script and I getting following errors:

    The variable name ‘@ReturnCode’ has already been declared. Variable names must be unique within a query batch or stored procedure.
    Msg 134, Level 15, State 1, Line 73
    The variable name ‘@jobId’ has already been declared. Variable names must be unique within a query batch or stored procedure.
    Msg 132, Level 15, State 1, Line 118

    So the question don’t we need to add after each job creation “go” statement?

Leave a comment

*