主页 / dba / 问题 / 241962
Accepted
Tom_W
Asked: 2019-07-04 03:12:40 +0800 CST

使用动态 SQL 创建 SQL 代理作业

  • 772

我目前正在使用 Ola Hallengren 脚本为数据库备份整理代理作业。我已经编写了一个脚本，该脚本创建了一个代理作业来执行完整备份，并且运行良好。

现在，我想让脚本动态化。为什么？每个环境都有不同的证书用于加密备份，并且清理时间可能会有所不同。

这就是我迄今为止所拥有的；

DECLARE @SQLCertificate NVARCHAR(50)
SET @SQLCertificate = N'00132'

EXECUTE ('
IF NOT EXISTS (SELECT [Name] FROM [dbo].[sysjobs] WHERE [Name] = N''Backups_Encrypted_FULL'')
BEGIN
    EXECUTE sp_add_job
        @job_name = N''Backups_Encrypted_FULL'',
        @enabled = 1,
        @notify_level_eventlog=2, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @owner_login_name = N''DBA_Maintenance'',
        @description = N''Performs a FULL backup of all User Databases'',
        @category_name = N''[Database Backups]''

    EXECUTE sp_add_jobstep  
        @step_id=1, 
        @job_name = N''Backups_Encrypted_FULL'',
        @step_name = N''Execute T-SQL'',
        @subsystem = N''TSQL'',
        @command = N''EXECUTE [dbo].[DatabaseBackup]
    @Databases = N''USER_DATABASES'',
    @Directory = N''C:\SQLBackup'',
    @DirectoryStructure = N''{DatabaseName}'',
    @FileName = ''{DatabaseName}_{Description}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}'',
    @BackupType = ''FULL'',
    @Description = N''Full_Backup'',
    @CheckSum = ''Y'',
    @Verify = ''Y'',
    @Compress = ''Y'',
    @CleanupTime = ''0'',
    @CleanupMode = ''AFTER_BACKUP'',
    @Encrypt = ''Y'',
    @EncryptionAlgorithm = ''AES_256'',
    @ServerCertificate = '''+ @SQLCertificate +''',
    @LogToTable = ''Y'''',
        @database_name = N''DBA.Maintenance''

    EXECUTE sp_add_jobschedule
        @job_name = N''Backups_Encrypted_FULL'',
        @name = N''Saturday Night'',
        @enabled = 1,
        @freq_type = 8,
        @freq_interval = 64,
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=1, 
        @active_end_date=99991231, 
        @active_start_time=220000, 
        @active_end_time=235959

    EXECUTE sp_add_jobserver
        @job_name = N''Backups_Encrypted_FULL'',
        @server_name = N''(Local)''
END ')

GO

但是，运行它会出现以下错误；

Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'USER_DATABASES'.

有人对此有任何想法吗？

sql-server dynamic-sql

2 个回答

  1. Best Answer

    第一个问题是您将转义的字符串分隔符包含在已经转义的字符串中：

    ----------------vv this opens a string
    @command = N''EXECUTE [dbo].[DatabaseBackup]
@Databases = N''USER_DATABASES'', 
--------------^^ this tries to close it

    这类似于以下内容：

    EXEC sys.sp_executesql N'PRINT ''foo''bar'';';

    产生：

    Msg 102, Level 15, State 1
    Incorrect syntax near 'bar'.
    Msg 105, Level 15, State 1
    Unclosed quotation mark after the character string ';'.

    要绕过它，您需要再次将第二层单引号加倍。

    EXEC sys.sp_executesql N'PRINT ''foo''''bar'';';

    哪个打印：

    foo'bar

    我想我不必告诉你，推杆 4、8、16 等等会变得非常丑陋，非常快。

    更好的解决方案

    在上面的评论中，Dan 对错误的原因不以为然，但在为什么首先要动态 SQL 的问题上绝对正确。这里根本不需要 - 您可以只使用命令的局部变量，并连接或替换证书名称。

    DECLARE @SQLCertificate nvarchar(50) = N'00132';

DECLARE @cmd nvarchar(max) = N'EXECUTE [dbo].[DatabaseBackup]
          @Databases = N''USER_DATABASES'',
          @Directory = N''C:\SQLBackup'',
          @DirectoryStructure = N''{DatabaseName}'',
          @FileName = ''{DatabaseName}_{Description}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}'',
          @BackupType = ''FULL'',
          @Description = N''Full_Backup'',
          @CheckSum = ''Y'',
          @Verify = ''Y'',
          @Compress = ''Y'',
          @CleanupTime = ''0'',
          @CleanupMode = ''AFTER_BACKUP'',
          @Encrypt = ''Y'',
          @EncryptionAlgorithm = ''AES_256'',
          @ServerCertificate = ''$cert$'',
          @LogToTable = ''Y'';';

SET @cmd = REPLACE(@cmd, N'$cert$', @SQLCertificate);

IF NOT EXISTS (SELECT 1 FROM [dbo].[sysjobs] WHERE [Name] = N'Backups_Encrypted_FULL')
BEGIN
    EXEC dbo.sp_add_job
        @job_name = N'Backups_Encrypted_FULL',
        @enabled = 1,
        @notify_level_eventlog = 2, 
        @notify_level_email = 0, 
        @notify_level_netsend = 0, 
        @notify_level_page = 0, 
        @delete_level = 0, 
        @owner_login_name = N'DBA_Maintenance',
        @description = N'Performs a FULL backup of all User Databases',
        @category_name = N'[Database Backups]';

    EXEC dbo.sp_add_jobstep  
        @step_id = 1, 
        @job_name = N'Backups_Encrypted_FULL',
        @step_name = N'Execute T-SQL',
        @subsystem = N'TSQL',
        @command = @cmd,
        @database_name = N'DBA.Maintenance';

    EXEC dbo.sp_add_jobschedule
        @job_name = N'Backups_Encrypted_FULL',
        @name = N'Saturday Night',
        @enabled = 1,
        @freq_type = 8,
        @freq_interval = 64,
        @freq_subday_interval = 0, 
        @freq_relative_interval = 0, 
        @freq_recurrence_factor = 1, 
        @active_end_date = 99991231, 
        @active_start_time = 220000, 
        @active_end_time = 235959;

    EXEC dbo.sp_add_jobserver
        @job_name = N'Backups_Encrypted_FULL',
        @server_name = N'(Local)';
END
    • 2

  2. 您必须另外引用：例如：@Databases = N''''USER_DATABASES'''',

    此外，如果出于调试目的将 exec(N'') 替换为 PRINT，您将看到脚本缺少引号

    • 1

相关问题