SQL Server日志传送T-SQL循环配置多数据库仅生成最后一个作业问题

问题现象 #

手动为单个数据库配置日志传送主端正常，但通过T-SQL循环批量配置时，仅最后一个数据库成功生成备份作业，其余数据库均提示日志传送配置失效，报错信息如下：

TITLE: Microsoft SQL Server Management Studio
------------------------------

The current log shipping configuration is not functional because the required backup job cannot be found. You must drop the current configuration by clearing the 'Enable this as a primary database in a log shipping configuration' checkbox. To resume log shipping, recreate the configuration using the same settings.

If you resume log shipping and you specify the backup and restore paths that you used before, any backup files from the old configuration that are uncopied or unrestored will be copied and restored by the new configuration.  Reinitializing the secondary databases is unnecessary.

------------------------------
ADDITIONAL INFORMATION:

The specified @job_id ('6A310814-0FEF-4C10-A738-4BBEB2BC2CEE') does not exist. (Microsoft SQL Server, Error: 14262)

原自动配置脚本 #

DECLARE @name NVARCHAR(256) -- database name  
 
DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT name 
FROM master.sys.databases AS sysdb
LEFT JOIN msdb.dbo.log_shipping_primary_databases AS log_shipping ON sysdb.name = log_shipping.primary_database
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
and log_shipping.primary_database is null
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN   

    DECLARE @LS_BackupJobId AS uniqueidentifier 
    DECLARE @LS_PrimaryId   AS uniqueidentifier 
    DECLARE @SP_Add_RetCode As int 

    DECLARE @databaseName NVARCHAR(200);
    SET @databaseName = N''+ @name + '';

    DECLARE @backupString NVARCHAR(20);
    SET @backupString = N'LSBackup_' + @databaseName;

    EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database 
        @database = @databaseName
        ,@backup_directory = N'/var/opt/mssql/shipping' 
        ,@backup_share = N'/var/opt/mssql/shipping' 
        ,@backup_job_name = @backupString
        ,@backup_retention_period = 360
        ,@backup_threshold = 60 
        ,@threshold_alert_enabled = 1
        ,@history_retention_period = 5760 
        ,@backup_job_id = @LS_BackupJobId OUTPUT 
        ,@primary_id = @LS_PrimaryId OUTPUT 
        ,@overwrite = 1 

    IF (@@ERROR = 0 AND @SP_Add_RetCode = 0) 
    BEGIN 

        
        DECLARE @LS_BackUpScheduleUID   As uniqueidentifier 
        DECLARE @LS_BackUpScheduleID    AS int 
                

        EXEC msdb.dbo.sp_add_schedule 
            @schedule_name =N'LSBackupSchedule_172.11.12.101' 
            ,@enabled = 1 
            ,@freq_type = 4 
            ,@freq_interval = 1 
            ,@freq_subday_type = 4 
            ,@freq_subday_interval = 15 
            ,@freq_recurrence_factor = 0 
            ,@active_start_date = 20241112 
            ,@active_end_date = 99991231 
            ,@active_start_time = 0 
            ,@active_end_time = 235900 
            ,@schedule_uid = @LS_BackUpScheduleUID OUTPUT 
            ,@schedule_id = @LS_BackUpScheduleID OUTPUT


        EXEC msdb.dbo.sp_attach_schedule 
            @job_id = @LS_BackupJobId 
            ,@schedule_id = @LS_BackUpScheduleID  

        EXEC msdb.dbo.sp_update_job 
            @job_id = @LS_BackupJobId 
            ,@enabled = 0 

    END 

    EXEC master.dbo.sp_add_log_shipping_alert_job 

    FETCH NEXT FROM db_cursor INTO @name   
END   
 
CLOSE db_cursor   
DEALLOCATE db_cursor

问题根源 #

1. 调度名重复：循环内每次执行sp_add_schedule都使用固定名称LSBackupSchedule_172.11.12.101，第一次执行后调度已存在，后续执行会失败，导致作业无法关联调度，最终作业创建不完整。
2. 作业名长度不足：@backupString定义为NVARCHAR(20)，若数据库名较长，会导致作业名被截断，系统无法识别对应的备份作业。
3. 冗余执行告警作业创建：sp_add_log_shipping_alert_job每次循环都执行，虽然该存储过程会判断告警作业是否已存在，但属于不必要的重复操作。

修改后的脚本 #

DECLARE @name NVARCHAR(256) -- database name  
DECLARE @LS_BackupJobId AS uniqueidentifier 
DECLARE @LS_PrimaryId   AS uniqueidentifier 
DECLARE @SP_Add_RetCode As int 
DECLARE @databaseName NVARCHAR(200);
DECLARE @backupString NVARCHAR(128); -- 加长长度避免截断
DECLARE @LS_BackUpScheduleUID   As uniqueidentifier 
DECLARE @LS_BackUpScheduleID    AS int 

-- 提前创建通用调度（如果不存在）
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.syschedules WHERE name = N'LSBackupSchedule_172.11.12.101')
BEGIN
    EXEC msdb.dbo.sp_add_schedule 
        @schedule_name =N'LSBackupSchedule_172.11.12.101' 
        ,@enabled = 1 
        ,@freq_type = 4 
        ,@freq_interval = 1 
        ,@freq_subday_type = 4 
        ,@freq_subday_interval = 15 
        ,@freq_recurrence_factor = 0 
        ,@active_start_date = 20241112 
        ,@active_end_date = 99991231 
        ,@active_start_time = 0 
        ,@active_end_time = 235900 
        ,@schedule_uid = @LS_BackUpScheduleUID OUTPUT 
        ,@schedule_id = @LS_BackUpScheduleID OUTPUT
END
ELSE
BEGIN
    -- 已存在则获取调度ID
    SELECT @LS_BackUpScheduleID = schedule_id 
    FROM msdb.dbo.syschedules 
    WHERE name = N'LSBackupSchedule_172.11.12.101'
END

DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT name 
FROM master.sys.databases AS sysdb
LEFT JOIN msdb.dbo.log_shipping_primary_databases AS log_shipping ON sysdb.name = log_shipping.primary_database
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
AND log_shipping.primary_database IS NULL
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN   
    SET @databaseName = @name;
    SET @backupString = N'LSBackup_' + @databaseName;

    EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database 
        @database = @databaseName
        ,@backup_directory = N'/var/opt/mssql/shipping' 
        ,@backup_share = N'/var/opt/mssql/shipping' 
        ,@backup_job_name = @backupString
        ,@backup_retention_period = 360
        ,@backup_threshold = 60 
        ,@threshold_alert_enabled = 1
        ,@history_retention_period = 5760 
        ,@backup_job_id = @LS_BackupJobId OUTPUT 
        ,@primary_id = @LS_PrimaryId OUTPUT 
        ,@overwrite = 1 

    IF (@@ERROR = 0 AND @SP_Add_RetCode = 0) 
    BEGIN 
        -- 关联已创建的调度
        EXEC msdb.dbo.sp_attach_schedule 
            @job_id = @LS_BackupJobId 
            ,@schedule_id = @LS_BackUpScheduleID  

        EXEC msdb.dbo.sp_update_job 
            @job_id = @LS_BackupJobId 
            ,@enabled = 0 
    END 

    FETCH NEXT FROM db_cursor INTO @name   
END   
 
CLOSE db_cursor   
DEALLOCATE db_cursor

-- 仅执行一次告警作业创建
EXEC master.dbo.sp_add_log_shipping_alert_job 

修改说明 #

1. 提前创建通用调度：在循环外判断调度是否存在，不存在则创建，避免重复创建导致的错误，后续循环直接关联该调度。
2. 加长作业名字段：将@backupString改为NVARCHAR(128)，避免数据库名较长时作业名被截断。
3. 优化变量声明：将重复声明的变量移到循环外，提高执行效率。
4. 移动告警作业创建：将sp_add_log_shipping_alert_job移到循环外，仅执行一次。

内容的提问来源于stack exchange，提问作者LDonSOvrfw