1
USE msdb;
2
GO
3

4
SET NOCOUNT ON;
5

6
-------------------------------------------------------------------------------
7
-- 參數區
8
-------------------------------------------------------------------------------
9
DECLARE @OverrideDatabaseName SYSNAME = N'你的新DB';   -- NULL = 不覆蓋 step database_name
10
DECLARE @OverrideOwnerLoginName SYSNAME = N'sa';       -- NULL = 沿用原 owner
11
DECLARE @OverrideOperatorName SYSNAME = NULL;          -- NULL = 沿用原 operator
12
DECLARE @DropIfExists BIT = 1;                         -- 1=若同名 Job 已存在，先刪除再重建
13
DECLARE @AttachToLocalServer BIT = 1;                  -- 1=最後執行 sp_add_jobserver 綁到 (LOCAL)
14

15
DECLARE @CRLF NVARCHAR(10) = CHAR(13) + CHAR(10) + '/**/';
16
DECLARE @FinalSQL NVARCHAR(MAX) = N'';
17
DECLARE @Header NVARCHAR(MAX) = N'';
18

19
-------------------------------------------------------------------------------
20
-- 指定要匯出的 Jobs
21
-------------------------------------------------------------------------------
22
DECLARE @Jobs TABLE
23
(
24
    RowNum INT IDENTITY(1,1) PRIMARY KEY,
25
    SourceJobName SYSNAME NOT NULL,
26
    TargetJobName SYSNAME NULL
27
);
28

29
-- ===== 在這裡填你要匯出的 Jobs =====
30
INSERT INTO @Jobs (SourceJobName, TargetJobName)
31
VALUES
32
    (N'Job_A', N'Job_A_New'),
33
    (N'Job_B', NULL),
34
    (N'Job_C', N'Job_C_New');
35
-- ==================================
36

37
-------------------------------------------------------------------------------
38
-- 基本檢查：來源 Job 是否存在
39
-------------------------------------------------------------------------------
40
IF EXISTS
41
(
42
    SELECT 1
43
    FROM @Jobs j
44
    LEFT JOIN msdb.dbo.sysjobs sj
45
        ON sj.name = j.SourceJobName
46
    WHERE sj.job_id IS NULL
47
)
48
BEGIN
49
    SELECT
50
        j.SourceJobName
51
    FROM @Jobs j
52
    LEFT JOIN msdb.dbo.sysjobs sj
53
        ON sj.name = j.SourceJobName
54
    WHERE sj.job_id IS NULL;
55

56
    RAISERROR(N'上面列出的 SourceJobName 在 msdb.dbo.sysjobs 中找不到，請先修正。', 16, 1);
57
    RETURN;
58
END;
59

60
-------------------------------------------------------------------------------
61
-- 檢查 TargetJobName 是否重複
62
-------------------------------------------------------------------------------
63
IF EXISTS
64
(
65
    SELECT 1
66
    FROM
67
    (
68
        SELECT ISNULL(TargetJobName, SourceJobName) AS FinalJobName
69
        FROM @Jobs
70
    ) x
71
    GROUP BY x.FinalJobName
72
    HAVING COUNT(*) > 1
73
)
74
BEGIN
75
    SELECT
76
        x.FinalJobName,
77
        COUNT(*) AS Cnt
78
    FROM
79
    (
80
        SELECT ISNULL(TargetJobName, SourceJobName) AS FinalJobName
81
        FROM @Jobs
82
    ) x
83
    GROUP BY x.FinalJobName
84
    HAVING COUNT(*) > 1;
85

86
    RAISERROR(N'上面列出的 TargetJobName / FinalJobName 有重複，會互相覆蓋，請先修正。', 16, 1);
87
    RETURN;
88
END;
89

90
-------------------------------------------------------------------------------
91
-- Header
92
-------------------------------------------------------------------------------
93
SET @Header =
94
N'/* =====================================================================' + @CRLF +
95
N'   SQL Agent Multi-Job Export Script' + @CRLF +
96
N'   Generated  : ' + CONVERT(NVARCHAR(19), GETDATE(), 120) + @CRLF +
97
N'   Notes:' + @CRLF +
98
N'   1. 請先確認新環境是否存在對應 Login / Operator / Proxy / Database。' + @CRLF +
99
N'   2. 若 step command 內文有寫死舊 DB，需人工調整。' + @CRLF +
100
N'   3. 建議先建立後停用 Job，手動測試 step 無誤再啟用 schedule。' + @CRLF +
101
N'===================================================================== */' + @CRLF + @CRLF;
102

103
SET @FinalSQL = @Header;
104

105
-------------------------------------------------------------------------------
106
-- 先準備待處理 Job 清單
107
-------------------------------------------------------------------------------
108
DECLARE @JobList TABLE
109
(
110
    RowNum INT PRIMARY KEY,
111
    SourceJobName SYSNAME NOT NULL,
112
    TargetJobName SYSNAME NOT NULL,
113
    JobId UNIQUEIDENTIFIER NOT NULL
114
);
115

116
INSERT INTO @JobList (RowNum, SourceJobName, TargetJobName, JobId)
117
SELECT
118
    j.RowNum,
119
    j.SourceJobName,
120
    ISNULL(j.TargetJobName, j.SourceJobName) AS TargetJobName,
121
    sj.job_id
122
FROM @Jobs j
123
JOIN msdb.dbo.sysjobs sj
124
    ON sj.name = j.SourceJobName;
125

126
-------------------------------------------------------------------------------
127
-- 逐一組 Script
128
-------------------------------------------------------------------------------
129
DECLARE
130
    @RowNum INT,
131
    @SourceJobName SYSNAME,
132
    @TargetJobName SYSNAME,
133
    @JobId UNIQUEIDENTIFIER,
134
    @JobSQL NVARCHAR(MAX),
135
    @Warnings NVARCHAR(MAX),
136
    @Tmp NVARCHAR(MAX);
137

138
DECLARE JobCursor CURSOR FAST_FORWARD FOR
139
SELECT RowNum, SourceJobName, TargetJobName, JobId
140
FROM @JobList
141
ORDER BY RowNum;
142

143
OPEN JobCursor;
144
FETCH NEXT FROM JobCursor INTO @RowNum, @SourceJobName, @TargetJobName, @JobId;
145

146
WHILE @@FETCH_STATUS = 0
147
BEGIN
148
    SET @JobSQL = N'';
149
    SET @Warnings = N'';
150
    SET @Tmp = N'';
151

152
    ---------------------------------------------------------------------------
153
    -- Job 區塊標頭
154
    ---------------------------------------------------------------------------
155
    SET @JobSQL = @JobSQL +
156
    N'/* =====================================================================' + @CRLF +
157
    N'   Job #' + CAST(@RowNum AS NVARCHAR(10)) + N'' + @CRLF +
158
    N'   Source Job : ' + REPLACE(@SourceJobName, '*/', '* /') + @CRLF +
159
    N'   Target Job : ' + REPLACE(@TargetJobName, '*/', '* /') + @CRLF +
160
    N'===================================================================== */' + @CRLF + @CRLF;
161

162
    ---------------------------------------------------------------------------
163
    -- Warning 區
164
    ---------------------------------------------------------------------------
165
    IF EXISTS (
166
        SELECT 1
167
        FROM msdb.dbo.sysjobsteps
168
        WHERE job_id = @JobId
169
        GROUP BY job_id
170
        HAVING COUNT(*) > 1
171
    )
172
    BEGIN
173
        SET @Warnings = @Warnings + N'-- WARNING: 此 Job 含多個 steps，請逐一確認 step 間跳轉。' + @CRLF;
174
    END;
175

176
    IF EXISTS (
177
        SELECT 1
178
        FROM msdb.dbo.sysjobsteps
179
        WHERE job_id = @JobId
180
          AND subsystem <> N'TSQL'
181
    )
182
    BEGIN
183
        SET @Warnings = @Warnings + N'-- WARNING: 含非 TSQL steps（如 CmdExec / PowerShell / SSIS），新環境需確認元件與權限。' + @CRLF;
184
    END;
185

186
    IF EXISTS (
187
        SELECT 1
188
        FROM msdb.dbo.sysjobsteps
189
        WHERE job_id = @JobId
190
          AND proxy_id IS NOT NULL
191
          AND proxy_id <> 0
192
    )
193
    BEGIN
194
        SET @Warnings = @Warnings + N'-- WARNING: 此 Job 使用 Proxy，新環境必須先建立對應 Proxy/Credential。' + @CRLF;
195
    END;
196

197
    IF EXISTS (
198
        SELECT 1
199
        FROM msdb.dbo.sysjobsteps
200
        WHERE job_id = @JobId
201
          AND (
202
                command LIKE N'%USE [%]%'
203
             OR command LIKE N'%].dbo.%'
204
             OR command LIKE N'%.dbo.%'
205
          )
206
    )
207
    BEGIN
208
        SET @Warnings = @Warnings + N'-- WARNING: 某些 step command 可能寫死資料庫名稱，請人工檢查 command 內容。' + @CRLF;
209
    END;
210

211
    IF @Warnings <> N''
212
    BEGIN
213
        SET @JobSQL = @JobSQL +
214
        N'/* Pre-check warnings */' + @CRLF +
215
        @Warnings + @CRLF;
216
    END;
217

218
    ---------------------------------------------------------------------------
219
    -- 刪除同名 Job（可選）
220
    ---------------------------------------------------------------------------
221
    SET @JobSQL = @JobSQL +
222
    N'USE msdb;' + @CRLF +
223
    N'GO' + @CRLF;
224

225
    IF @DropIfExists = 1
226
    BEGIN
227
        SET @JobSQL = @JobSQL +
228
        N'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N''' + REPLACE(@TargetJobName, '''', '''''') + N''')' + @CRLF +
229
        N'BEGIN' + @CRLF +
230
        N'    EXEC msdb.dbo.sp_delete_job @job_name = N''' + REPLACE(@TargetJobName, '''', '''''') + N''';' + @CRLF +
231
        N'END' + @CRLF +
232
        N'GO' + @CRLF;
233
    END;
234

235
    SET @JobSQL = @JobSQL +
236
    N'DECLARE @jobId BINARY(16);' + @CRLF + @CRLF;
237

238
    ---------------------------------------------------------------------------
239
    -- sp_add_job
240
    ---------------------------------------------------------------------------
241
    SELECT @Tmp =
242
    N'EXEC msdb.dbo.sp_add_job' + @CRLF +
243
    N'    @job_name = N''' + REPLACE(@TargetJobName, '''', '''''') + N''',' + @CRLF +
244
    N'    @enabled = ' + CAST(j.enabled AS NVARCHAR(10)) + N',' + @CRLF +
245
    N'    @notify_level_eventlog = ' + CAST(j.notify_level_eventlog AS NVARCHAR(10)) + N',' + @CRLF +
246
    N'    @notify_level_email = ' + CAST(j.notify_level_email AS NVARCHAR(10)) + N',' + @CRLF +
247
    N'    @notify_level_netsend = ' + CAST(j.notify_level_netsend AS NVARCHAR(10)) + N',' + @CRLF +
248
    N'    @notify_level_page = ' + CAST(j.notify_level_page AS NVARCHAR(10)) + N',' + @CRLF +
249
    N'    @delete_level = ' + CAST(j.delete_level AS NVARCHAR(10)) + N',' + @CRLF +
250
    N'    @description = N''' + REPLACE(ISNULL(j.description, N''), '''', '''''') + N''',' + @CRLF +
251
    N'    @category_name = N''' + REPLACE(ISNULL(c.name, N'[Uncategorized (Local)]'), '''', '''''') + N''',' + @CRLF +
252
    N'    @owner_login_name = N''' + REPLACE(ISNULL(@OverrideOwnerLoginName, SUSER_SNAME(j.owner_sid)), '''', '''''') + N''',' + @CRLF +
253
    N'    @job_id = @jobId OUTPUT;' + @CRLF + @CRLF
254
    FROM msdb.dbo.sysjobs j
255
    LEFT JOIN msdb.dbo.syscategories c
256
        ON j.category_id = c.category_id
257
    WHERE j.job_id = @JobId;
258

259
    SET @JobSQL = @JobSQL + ISNULL(@Tmp, N'');
260

261
    ---------------------------------------------------------------------------
262
    -- Steps
263
    ---------------------------------------------------------------------------
264
    SET @Tmp = N'';
265

266
    SELECT @Tmp = @Tmp +
267
    N'EXEC msdb.dbo.sp_add_jobstep' + @CRLF +
268
    N'    @job_id = @jobId,' + @CRLF +
269
    N'    @step_id = ' + CAST(s.step_id AS NVARCHAR(10)) + N',' + @CRLF +
270
    N'    @step_name = N''' + REPLACE(s.step_name, '''', '''''') + N''',' + @CRLF +
271
    N'    @subsystem = N''' + REPLACE(s.subsystem, '''', '''''') + N''',' + @CRLF +
272
    N'    @command = N''' + REPLACE(ISNULL(s.command, N''), '''', '''''') + N''',' + @CRLF +
273
    CASE
274
        WHEN s.additional_parameters IS NULL THEN N''
275
        ELSE N'    @additional_parameters = N''' + REPLACE(s.additional_parameters, '''', '''''') + N''',' + @CRLF
276
    END +
277
    N'    @cmdexec_success_code = ' + CAST(ISNULL(s.cmdexec_success_code, 0) AS NVARCHAR(10)) + N',' + @CRLF +
278
    N'    @on_success_action = ' + CAST(s.on_success_action AS NVARCHAR(10)) + N',' + @CRLF +
279
    N'    @on_success_step_id = ' + CAST(s.on_success_step_id AS NVARCHAR(10)) + N',' + @CRLF +
280
    N'    @on_fail_action = ' + CAST(s.on_fail_action AS NVARCHAR(10)) + N',' + @CRLF +
281
    N'    @on_fail_step_id = ' + CAST(s.on_fail_step_id AS NVARCHAR(10)) + N',' + @CRLF +
282
    CASE
283
        WHEN s.server IS NULL THEN N''
284
        ELSE N'    @server = N''' + REPLACE(s.server, '''', '''''') + N''',' + @CRLF
285
    END +
286
    N'    @database_name = ' +
287
        CASE
288
            WHEN ISNULL(@OverrideDatabaseName, s.database_name) IS NULL THEN N'NULL'
289
            ELSE N'N''' + REPLACE(ISNULL(@OverrideDatabaseName, s.database_name), '''', '''''') + N''''
290
        END + N',' + @CRLF +
291
    N'    @database_user_name = ' +
292
        CASE
293
            WHEN s.database_user_name IS NULL THEN N'NULL'
294
            ELSE N'N''' + REPLACE(s.database_user_name, '''', '''''') + N''''
295
        END + N',' + @CRLF +
296
    N'    @retry_attempts = ' + CAST(ISNULL(s.retry_attempts, 0) AS NVARCHAR(10)) + N',' + @CRLF +
297
    N'    @retry_interval = ' + CAST(ISNULL(s.retry_interval, 0) AS NVARCHAR(10)) + N',' + @CRLF +
298
    N'    @os_run_priority = ' + CAST(ISNULL(s.os_run_priority, 0) AS NVARCHAR(10)) + N',' + @CRLF +
299
    N'    @output_file_name = ' +
300
        CASE
301
            WHEN s.output_file_name IS NULL THEN N'NULL'
302
            ELSE N'N''' + REPLACE(s.output_file_name, '''', '''''') + N''''
303
        END + N',' + @CRLF +
304
    CASE
305
        WHEN p.name IS NULL OR p.name = N'' THEN N''
306
        ELSE N'    @proxy_name = N''' + REPLACE(p.name, '''', '''''') + N''',' + @CRLF
307
    END +
308
    N'    @flags = ' + CAST(ISNULL(s.flags, 0) AS NVARCHAR(10)) + N';' + @CRLF + @CRLF
309
    FROM msdb.dbo.sysjobsteps s
310
    LEFT JOIN msdb.dbo.sysproxies p
311
        ON s.proxy_id = p.proxy_id
312
    WHERE s.job_id = @JobId
313
    ORDER BY s.step_id;
314

315
    SET @JobSQL = @JobSQL + ISNULL(@Tmp, N'');
316

317
    ---------------------------------------------------------------------------
318
    -- start_step_id
319
    ---------------------------------------------------------------------------
320
    SELECT @Tmp =
321
    N'EXEC msdb.dbo.sp_update_job' + @CRLF +
322
    N'    @job_id = @jobId,' + @CRLF +
323
    N'    @start_step_id = ' + CAST(j.start_step_id AS NVARCHAR(10)) + N';' + @CRLF + @CRLF
324
    FROM msdb.dbo.sysjobs j
325
    WHERE j.job_id = @JobId;
326

327
    SET @JobSQL = @JobSQL + ISNULL(@Tmp, N'');
328

329
    ---------------------------------------------------------------------------
330
    -- schedules
331
    ---------------------------------------------------------------------------
332
    SET @Tmp = N'';
333

334
    SELECT @Tmp = @Tmp +
335
    N'EXEC msdb.dbo.sp_add_jobschedule' + @CRLF +
336
    N'    @job_id = @jobId,' + @CRLF +
337
    N'    @name = N''' + REPLACE(sch.name, '''', '''''') + N''',' + @CRLF +
338
    N'    @enabled = ' + CAST(sch.enabled AS NVARCHAR(10)) + N',' + @CRLF +
339
    N'    @freq_type = ' + CAST(sch.freq_type AS NVARCHAR(10)) + N',' + @CRLF +
340
    N'    @freq_interval = ' + CAST(sch.freq_interval AS NVARCHAR(10)) + N',' + @CRLF +
341
    N'    @freq_subday_type = ' + CAST(sch.freq_subday_type AS NVARCHAR(10)) + N',' + @CRLF +
342
    N'    @freq_subday_interval = ' + CAST(sch.freq_subday_interval AS NVARCHAR(10)) + N',' + @CRLF +
343
    N'    @freq_relative_interval = ' + CAST(sch.freq_relative_interval AS NVARCHAR(10)) + N',' + @CRLF +
344
    N'    @freq_recurrence_factor = ' + CAST(sch.freq_recurrence_factor AS NVARCHAR(10)) + N',' + @CRLF +
345
    N'    @active_start_date = ' + CAST(sch.active_start_date AS NVARCHAR(20)) + N',' + @CRLF +
346
    N'    @active_end_date = ' + CAST(sch.active_end_date AS NVARCHAR(20)) + N',' + @CRLF +
347
    N'    @active_start_time = ' + CAST(sch.active_start_time AS NVARCHAR(20)) + N',' + @CRLF +
348
    N'    @active_end_time = ' + CAST(sch.active_end_time AS NVARCHAR(20)) + N';' + @CRLF + @CRLF
349
    FROM
350
    (
351
        SELECT DISTINCT
352
            sch.name,
353
            sch.enabled,
354
            sch.freq_type,
355
            sch.freq_interval,
356
            sch.freq_subday_type,
357
            sch.freq_subday_interval,
358
            sch.freq_relative_interval,
359
            sch.freq_recurrence_factor,
360
            sch.active_start_date,
361
            sch.active_end_date,
362
            sch.active_start_time,
363
            sch.active_end_time
364
        FROM msdb.dbo.sysjobschedules js
365
        JOIN msdb.dbo.sysschedules sch
366
            ON js.schedule_id = sch.schedule_id
367
        WHERE js.job_id = @JobId
368
    ) sch
369
    ORDER BY sch.name;
370

371
    SET @JobSQL = @JobSQL + ISNULL(@Tmp, N'');
372

373
    ---------------------------------------------------------------------------
374
    -- operator / notify
375
    ---------------------------------------------------------------------------
376
    SELECT @Tmp =
377
    N'EXEC msdb.dbo.sp_update_job' + @CRLF +
378
    N'    @job_id = @jobId,' + @CRLF +
379
    N'    @notify_level_email = ' + CAST(j.notify_level_email AS NVARCHAR(10)) + N',' + @CRLF +
380
    N'    @notify_level_netsend = ' + CAST(j.notify_level_netsend AS NVARCHAR(10)) + N',' + @CRLF +
381
    N'    @notify_level_page = ' + CAST(j.notify_level_page AS NVARCHAR(10)) +
382
    CASE
383
        WHEN ISNULL(@OverrideOperatorName, op_email.name) IS NOT NULL
384
            THEN N',' + @CRLF + N'    @notify_email_operator_name = N''' + REPLACE(ISNULL(@OverrideOperatorName, op_email.name), '''', '''''') + N''''
385
        ELSE N''
386
    END +
387
    CASE
388
        WHEN ISNULL(@OverrideOperatorName, op_netsend.name) IS NOT NULL
389
            THEN N',' + @CRLF + N'    @notify_netsend_operator_name = N''' + REPLACE(ISNULL(@OverrideOperatorName, op_netsend.name), '''', '''''') + N''''
390
        ELSE N''
391
    END +
392
    CASE
393
        WHEN ISNULL(@OverrideOperatorName, op_page.name) IS NOT NULL
394
            THEN N',' + @CRLF + N'    @notify_page_operator_name = N''' + REPLACE(ISNULL(@OverrideOperatorName, op_page.name), '''', '''''') + N''''
395
        ELSE N''
396
    END +
397
    N';' + @CRLF + @CRLF
398
    FROM msdb.dbo.sysjobs j
399
    LEFT JOIN msdb.dbo.sysoperators op_email
400
        ON j.notify_email_operator_id = op_email.id
401
    LEFT JOIN msdb.dbo.sysoperators op_netsend
402
        ON j.notify_netsend_operator_id = op_netsend.id
403
    LEFT JOIN msdb.dbo.sysoperators op_page
404
        ON j.notify_page_operator_id = op_page.id
405
    WHERE j.job_id = @JobId;
406

407
    SET @JobSQL = @JobSQL + ISNULL(@Tmp, N'');
408

409
    ---------------------------------------------------------------------------
410
    -- job server
411
    ---------------------------------------------------------------------------
412
    IF @AttachToLocalServer = 1
413
    BEGIN
414
        SET @JobSQL = @JobSQL +
415
        N'EXEC msdb.dbo.sp_add_jobserver' + @CRLF +
416
        N'    @job_id = @jobId,' + @CRLF +
417
        N'    @server_name = N''(LOCAL)'';' + @CRLF + @CRLF;
418
    END;
419

420
    ---------------------------------------------------------------------------
421
    -- step review
422
    ---------------------------------------------------------------------------
423
    SET @JobSQL = @JobSQL +
424
    N'/* Step Review' + @CRLF;
425

426
    SET @Tmp = N'';
427

428
    SELECT @Tmp = @Tmp +
429
    N'-- Step ' + CAST(s.step_id AS NVARCHAR(10)) +
430
    N' | Name: ' + REPLACE(REPLACE(s.step_name, CHAR(13), N' '), CHAR(10), N' ') +
431
    N' | Subsystem: ' + s.subsystem +
432
    N' | DB: ' + ISNULL(s.database_name, N'(NULL)') +
433
    N' | Proxy: ' + ISNULL(p.name, N'(NULL)') +
434
    CASE
435
        WHEN s.command LIKE N'%USE [%]%'
436
          OR s.command LIKE N'%].dbo.%'
437
          OR s.command LIKE N'%.dbo.%'
438
        THEN N' | CHECK_COMMAND_FOR_DB_NAME=YES'
439
        ELSE N''
440
    END +
441
    @CRLF
442
    FROM msdb.dbo.sysjobsteps s
443
    LEFT JOIN msdb.dbo.sysproxies p
444
        ON s.proxy_id = p.proxy_id
445
    WHERE s.job_id = @JobId
446
    ORDER BY s.step_id;
447

448
    SET @JobSQL = @JobSQL + ISNULL(@Tmp, N'') +
449
    N'*/' + @CRLF +
450
    N'GO' + @CRLF + @CRLF;
451

452
    ---------------------------------------------------------------------------
453
    -- 加到總腳本
454
    ---------------------------------------------------------------------------
455
    SET @FinalSQL = @FinalSQL + @JobSQL;
456

457
    FETCH NEXT FROM JobCursor INTO @RowNum, @SourceJobName, @TargetJobName, @JobId;
458
END
459

460
CLOSE JobCursor;
461
DEALLOCATE JobCursor;
462

463
-------------------------------------------------------------------------------
464
-- 最終輸出
465
-------------------------------------------------------------------------------
466
SELECT @FinalSQL AS DeployScript;
467
GO