Início / dba / Perguntas / 241962
Accepted
Tom_W
Asked: 2019-07-04 03:12:40 +0800 CST

Criando um trabalho do SQL Agent usando SQL dinâmico

  • 772

Atualmente, estou montando trabalhos de agente para backups de banco de dados, usando os scripts Ola Hallengren. Montei um script que cria um Agent Job para realizar backups COMPLETOS e está funcionando perfeitamente.

Agora, quero tornar o script Dinâmico. Por quê? Cada ambiente terá um Certificado diferente para criptografar os backups e os tempos de limpeza podem variar.

Isto é o que tenho até agora;

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

Executá-lo no entanto, dá o seguinte erro;

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

Alguém tem alguma opinião sobre isso?

sql-server dynamic-sql

2 respostas

  1. Best Answer

    O primeiro problema é que você está colocando delimitadores de string com escape dentro de uma string já com escape:

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

    Isso é semelhante ao seguinte:

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

    Que rende:

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

    Para contornar isso, você precisa dobrar a segunda camada de aspas simples novamente .

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

    Que imprime:

    foo'bar

    Acho que não preciso dizer que colocar quatro, oito, dezesseis e assim por diante fica muito feio, muito rápido.

    Uma solução melhor

    No comentário acima, Dan estava errado sobre o motivo do erro, mas absolutamente certo na questão de por que o SQL dinâmico em primeiro lugar. Simplesmente não é necessário aqui - você pode simplesmente usar uma variável local para o comando e concatenar ou substituir o nome do certificado.

    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. você tem que citar adicionalmente: exemplo: @Databases = N''''USER_DATABASES'''',

    além disso, se você substituir exec(N'') por PRINT para fins de depuração, verá que o script não possui aspas

    • 1

relate perguntas