• http://sqlmd.wordpress.com/ Mike Donnelly

    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.

    • http://www.johnsansom.com/ John Sansom

      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.

  • http://twitter.com/Samyan26 Samuel Yanzu

    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?

    • http://www.johnsansom.com/ John Sansom

      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!

  • SoftwareMark

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

    Thanks for sharing!

    • http://www.johnsansom.com/ John Sansom

      My pleasure sir. Thanks for your comment.

  • mbourgon

    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.  

    • http://www.johnsansom.com/ John Sansom

      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.

  • Tjaybelt

    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.

    • http://www.johnsansom.com/ John Sansom

      Tjay, my pleasure sir! It’s always great to hear from folks who have been able to put my dodgy code to good use ;-) Thanks for your comments.

  • Khellian

    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. 

    • http://www.johnsansom.com/ John Sansom

      Khellian, you’re welcome. Always great to hear of another victory in the battle to automate everything and save time. Thanks for your comments!

  • Chintan

    Great Script!! Thanks for Sharing John!!

    • http://www.johnsansom.com John Sansom

      My pleasure sir, glad you found it useful. Thanks for your comments!

  • jesse

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

    • http://www.johnsansom.com John Sansom

      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.

      • Shafi

        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.

        • http://www.johnsansom.com John Sansom

          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.

          • Robbert Hof

            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 ;)

            • http://www.johnsansom.com John Sansom

              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.

  • Pingback: PowerShell Script to Configure SQL Server Storage()

  • Jimbo

    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

  • Pingback: Restart the SQL Server Agent Service using PowerShell()

  • Vijay

    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,

    • http://www.johnsansom.com John Sansom

      Vijay – Take a look at the following article for instructions on how to get started: PowerShell the SQL Server Way.

      Let me know how you get on.

  • S

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

    • http://www.johnsansom.com John Sansom

      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.

  • Vijay

    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.

    • http://www.johnsansom.com John Sansom

      Vijay – You’re welcome. Glad I could help.

  • http://gravatar.com/neptun2000 Michael

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

    • http://www.johnsansom.com John Sansom

      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?

  • David

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

    • http://www.johnsansom.com John Sansom

      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.

  • http://gravatar.com/neptun2000 Michael

    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?

    • http://www.johnsansom.com John Sansom

      Michael – Good call sir. I’ve made a modification to the script to include the “GO” command to signal the end of each T-SQL batch.

  • sagar

    I am looking to script out individual jobs. In 2014 we will can have different replicas. We can create all jobs at a time and disable them but we need to create new jobs across all environments. Any suggestions ?

    • http://www.johnsansom.com John Sansom

      How about creating a single script containing all the SQL Server Agent Jobs that you wish to create and then executing it against all the required SQL Server Instances in one go by using a Central Management Server (CMS) query (http://msdn.microsoft.com/en-us/library/bb964743.aspx).

  • http://koetjeboeh.nl/ Fabio Bruna

    Ah, thanks a lot! Saved me a lot of trouble. Starting to like powershell a lot(coming from Unix). First found a ps script to store DDL in files, and now all SQL agent jobs. Great, thanks again.

    • http://www.johnsansom.com/ John Sansom

      Fabio, that’s great! Delighted you found it helpful.

  • devesh_srivastava

    Hi John, Could please help me in one scenario – I need to script out the sql agent jobs which are contained in a text file unlike the scenario in your where we are scrpting all the jobs on the server. So, the job name would come as an input from a text file and then all those jobs should be scripted in one single file.

    Please help me John as this kind of urgent for me and I am new to SMO- just a kid!


    • http://www.johnsansom.com/ John Sansom

      Hi Devesh – You need to break the problem you are trying to solve down into discrete and manageable steps.

      Think about what you would need to change to the script above in order to make it do what you want. There are lots of PowerShell scripts on the Internet that take a text file as input. You could quite easily use one as guide to create your own next steps.

      What have you tried so far?

      • devesh_srivastava

        $srv.JobServer.Jobs | foreach {$_.Script() + “GO`r`n”} | out-file “.$OutputFolderjobs.sql”
        I certainly know in the above line need some changes s that it accepts the names from the text file and pass it onto Script(). Since it requires basics of SMO and programming, I am struggling to get that into place. I am just a newbie to programming. May be your suggestion in context with the above code line could help me get somewhere.

  • Peter

    Thanks for the script! I’m not very familar with PowerShell so I hope you can help me:

    I replace the ‘replace character’ option to ‘-replace ‘::’, ”’ because I got many jobs with ‘::’.
    If I start it I get a ‘FileOpenFailure,Microsoft.PowerShell.Commands.OutFileCommand’ and ‘FileNotFoundException’.

    My second problem:
    Because I’m not familar with PowerShell: Could you extend the “-replace” for all forbidden characters?

    • Nathan J

      You can extend the string replacement by simply adding more “-replace” arguments. The following has fixed most of the issues I’ve come across so far, though I’m sure there are more:

      $($_.Name -replace ‘\’, ” -replace ‘/’,” -replace ‘[‘, ” -replace ‘]’, ” -replace ‘:’, ”) + “.sql”)

  • http://centralpa.sqlpass.org Dustin W. Jones

    great script, thanks for sharing, came in handy for me