Creating Job Backup

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

USE msdb
GO

/**********************************************************************************
Stored Procedure......: pCreateBackupJobs
Objetivo..............: Criar devices para backup full, differential e log

----------  --------------------  -------------------------------------------------
Data    Programador  Observação
----------  --------------------  -------------------------------------------------
22/07/2002  Paulo Barros  Created

**********************************************************************************/CREATE PROCEDURE pCreateBackupJobs AS
/*********************************************************************************/
-- Cria 4 jobs
EXEC sp_add_job @job_name = 'Full Backup', 
    @enabled = 1,
    @description = 'Backup Full all user Databases',
    @owner_login_name = 'sa',
    @notify_level_eventlog = 2,
    @notify_level_email = 2,
    @notify_email_operator_name = 'SQLAdmin'

EXEC sp_add_job @job_name = 'Backup Diferencial', 
    @enabled = 1,
    @description = 'Backup Differential all user Databases',
    @owner_login_name = 'sa',
    @notify_level_eventlog = 2,
    @notify_level_email = 2,
    @notify_email_operator_name = 'SQLAdmin'

EXEC sp_add_job @job_name = 'Backup Log - Initialization', 
    @enabled = 1,
    @description = 'Backup Log all user Databases',
    @owner_login_name = 'sa',
    @notify_level_eventlog = 2,
    @notify_level_email = 2,
    @notify_email_operator_name = 'SQLAdmin'

EXEC sp_add_job @job_name = 'Backup Log', 
    @enabled = 1,
    @description = 'Backup de Log all user Databases',
    @owner_login_name = 'sa',
    @notify_level_eventlog = 2,
    @notify_level_email = 2,
    @notify_email_operator_name = 'SQLAdmin'

Declare @cDBName varchar(50), @nRecs tinyint, @nCount tinyint, @nAction char(1)

Declare curDB CURSOR FOR
select name from master..sysdatabases
where name not in ('tempdb','master','model','msdb')

Set @nCount = 0
Set @nAction = '3'    -- goto next step

-- Nº de registros
select @nRecs = Count(name) from master..sysdatabases
where name not in ('tempdb','master','model','msdb')

OPEN curDB 

FETCH NEXT FROM curDB 
INTO @cDBName

-- Crias os passos de cada Job
WHILE @@FETCH_STATUS = 0
BEGIN

Declare @cComando varchar(1000)

Set @nCount = @nCount + 1

-- Se for o último passo do job
if @nCount = @nRecs
   Set @nAction = '1'   -- Sai com sucesso

-- Cria um step no job de Backup Full para cada DB
set @cComando = "sp_add_jobstep @job_name  = 'Backup Completo',
           @step_name = " + @cDBName + ",
             @subsystem = 'TSQL', 
             @command   = 'BACKUP DATABASE " + @cDBName + " TO " + 
       @cDBName + "_Full WITH INIT',
         @database_name = " + @cDBName + ",
 @on_success_action = " + @nAction + ", 
         @output_file_name = 'D:\MSSQL\JOBS\" + @cDBName + "_BKPFULL.TXT'"

EXEC (@cComando)

-- Cria um step no job de Backup Diferencial para cada DB
set @cComando = "sp_add_jobstep @job_name  = 'Backup Diferencial',
           @step_name = " + @cDBName + ",
             @subsystem = 'TSQL', 
             @command   = 'BACKUP DATABASE " + @cDBName + " TO " + 
       @cDBName + "_Diff WITH DIFFERENTIAL, INIT',
         @database_name = " + @cDBName + ",
 @on_success_action = " + @nAction + ", 
         @output_file_name = 'D:\MSSQL\JOBS\" + @cDBName + "_BKPDIFF.TXT'"

EXEC (@cComando)

-- Cria um step no job de Backup de Log - Inicialização para cada DB
set @cComando = "sp_add_jobstep @job_name  = 'Backup de Log - Inicialização',
           @step_name = " + @cDBName + ",
             @subsystem = 'TSQL', 
             @command   = 'BACKUP LOG " + @cDBName + " TO " + 
       @cDBName + "_Log WITH INIT',
         @database_name = " + @cDBName + ",
 @on_success_action = " + @nAction + ", 
         @output_file_name = 'D:\MSSQL\JOBS\" + @cDBName + "_BKPLOGINIT.TXT'"

EXEC (@cComando)

-- Cria um step no job de Backup Full para cada DB
set @cComando = "sp_add_jobstep @job_name  = 'Backup de Log',
           @step_name = " + @cDBName + ",
             @subsystem = 'TSQL', 
             @command   = 'BACKUP LOG " + @cDBName + " TO " + 
       @cDBName + "_Log WITH NOINIT',
         @database_name = " + @cDBName + ",
 @on_success_action = " + @nAction + ", 
         @output_file_name = 'D:\MSSQL\JOBS\" + @cDBName + "_BKPLOG.TXT'"

EXEC (@cComando)
/*
    -- Parâmetros adicionais do sp_add_jobstep
    [,[@additional_parameters =] 'parameters']
    [,[@cmdexec_success_code =] code]
    [,[@on_success_action =] success_action]
    [,[@on_success_step_id =] success_step_id]
    [,[@on_fail_action =] fail_action]
    [,[@on_fail_step_id =] fail_step_id] 
    [,[@server =] 'server']
    [,[@database_name =] 'database'] 
    [,[@database_user_name =] 'user']
    [,[@retry_attempts =] retry_attempts] 
    [,[@retry_interval =] retry_interval]
    [,[@os_run_priority =] run_priority] 
    [,[@output_file_name =] 'file_name']
    [,[@flags =] flags]
*/
    FETCH NEXT FROM curDB 

    INTO @cDBName

END

DEALLOCATE curDB




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO