SQL Server - How to back up all SQL Agent jobs via command line (CLR C # or Powershell)

Views: 2.875
Reading Time: 8 minutes

Hello everybody!
All right?

In this post I will demonstrate how to back up all SQL Server Agent jobs via command line (CLR C # or Powershell) and export the results to SQL scripts. You can choose to generate 1 script for each job or 1 single script with all jobs in your instance.

During the migration from SQL Server 2008 R2 to 2014 this script was especially useful since in the report server instance there were little more than 700 jobs and the task of manually saving each script was not feasible. Backing up / restoring the msdb database could be a solution (although I don't find it very recommended), but since these were different versions of SQL Server, we discarded this hypothesis.

Remember that before you can restore jobs in the new environment, you must have created logins on the new instance. If you do not know how to backup logins and their permissions, please visit the post. SQL Server - How to transfer logins between instances by backing up users, logins, and permissions.

Prerequisites

As prerequisites for using the Stored Procedure stpJob_Backup, you will also need to create the following objects:

Run ScriptPowerShell (Click here to access the post)
View source

Server.cs file source code
View source

Return class source code (Click here to access the post)
View source

CLR Procedure (C #) source code stpJob_Backup

Examples of use

Now I will demonstrate how to use the Stored Procedure created to export the SQL script from all SQL Server Agent jobs. These are the current instance jobs.

Example 1:

In this example, I will demonstrate how to export the creation script for all instance jobs, being 1 sql file for each job.

Result:

Example 2:

In this example, I will demonstrate how to export the creation script of all instance jobs, being 1 sql file containing the script of all jobs.

Result:

Using PowerShell and OLE Automation Only

If you do not want to use CLR for this solution and only want to use PowerShell for the database, you can do so as well.

Prerequisites

As prerequisites for using the procedure below, we will need some file manipulation functions. For this, we will use OLE Automation to manipulate these files. To learn more about this, see the post. File Operations Using OLE Automation in SQL Server.

stpWrite_File_File
View source

fncFile_Exists_File
View source

stpApaga_File_File
View source

Stored Procedure stpExecuta_Script_Powershell

Example of use:

Result:

Using PowerShell Only

If you only want to use PowerShell script manually, follow the full script below.

That's it, guys.
A big hug and until next time.

SQL Server - How to backup all SQL Agent jobs via command line (CLR C # or Powershell) backup restore sql server agent jobs command line

SQL Server - How to backup all SQL Agent jobs via command line (CLR C # or Powershell) backup restore sql server agent jobs command line