CODEWITHSUNDEEP
sql-serverpowershellssmssql-server-2014
Bastián SN
Bastián SN

Reputation: 405

How to extract ETL (Jobs) from SQL Server Management Studio

I want to extract the code from my Jobs (ETL) from my SQL Server 2014, which are in SQL Server Agent -> Jobs. I currently have more than 400, is there a faster way than entering each one and saving the code?

Jobs

I have tried various codes from ChatGPT or Gemini and it does not work, it does not show me the codes that I have in each Job (ETL)

    DECLARE @jobname sysname, @filename varchar(255), @job_id uniqueidentifier, @sql nvarchar(max);

DECLARE job_cursor CURSOR FOR
SELECT name, job_id FROM msdb.dbo.sysjobs;

OPEN job_cursor;

FETCH NEXT FROM job_cursor INTO @jobname, @job_id;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @filename = CONCAT('C:\Scripts\Jobs\', @jobname, '.sql');

    -- Construct the command to generate the script of the job
    SET @sql = 'EXEC msdb.dbo.sp_helpjob @job_id = ''' + CAST(@job_id AS varchar(36)) + ''';';

    -- Execute the command and redirect the output to a file, using Windows authentication
    EXEC xp_cmdshell CONCAT('sqlcmd -S your_server_name -d msdb -Q "', @sql, '" -o "', @filename, '"');

    FETCH NEXT FROM job_cursor INTO @jobname, @job_id;
END

CLOSE job_cursor;
DEALLOCATE job_cursor;

Upvotes: 0

Views: 92

Answers (1)

Alex
Alex

Reputation: 5157

In SSMS you can select multiple Jobs to be "extracted" into a single script:

  1. In "Object Explorer" select "Jobs":

Jobs folder selected in Object Explorer

  1. Press "F7" to open "Object Explorer Details" (you can also open it via View->Object Explorer Details):

Job list in Object Explorer Details

  1. Select all of the Jobs you want to export and right click to bring up a pop up menu. Select "Script Job As" and so on.

enter image description here

P.S. This trick works for many things

Upvotes: 0

Related Questions