Create Backup Objects and Jobs

October 31, 2016
This script will create backup objects and jobs in a SQLDBA database: a config table, a backup log, an exclusion list, the backup, copy, archive and retention procedures, and SQL Agent jobs for full, differential, log and clean-up. Backups are written locally, then copied to a central share. Change \\backupserver\ to your backup share. Tested on SQL Server 2022.
Before you create backup objects and jobs

The copy, archive and clean-up procedures use xp_cmdshell, so it must be enabled and the SQL Server service account needs write access to both backup locations.
The differential job is created disabled. Enable it if you want differentials between full backups.
Add any database you don’t want backed up to Backups.Database_Exclusion_List.
If you’d rather not maintain your own backup procedures, Ola Hallengren’s DatabaseBackup does the same job. Microsoft covers the backup options in its backup and restore documentation. To restore one of these backups, see Restoring a Full Database Backup (SSMS).
The script

/*
Back up the master, msdb, and model databases to ensure that you can roll back to a
previous configuration if problems occur.

master: This database hosts system-level information. Back up this database regularly.

model: You need to back up the template database only after you have made modifications.
Because changes to the template database are usually infrequent, this database
does not need to be backed up regularly.The model database serves as the template for all new databases that you create on

msdb: This database hosts scheduling data for alerts and jobs and hosts operator
information. It also stores the backup and restore history tables. You should back up
this database whenever you make changes to jobs and alerts. Integration Services stores packages jobs

tempdb: This database is re-created each time the Database Engine starts. You cannot
back up the tempdb database.

*/
IF NOT EXISTS
(
    SELECT *
    FROM sys.databases
    WHERE name = 'SQLDBA'
)
    BEGIN
        CREATE DATABASE [SQLDBA];
END;
GO
USE SQLDBA;
GO
IF NOT EXISTS
(
    SELECT *
    FROM sys.schemas
    WHERE name = 'Backups'
)
    BEGIN
        EXEC ('CREATE SCHEMA [Backups]');
END;
USE [SQLDBA];
GO
IF
(
    SELECT OBJECT_ID('Backups.Backup_Config')
) IS NULL
    BEGIN
        CREATE TABLE [Backups].[Backup_Config]
        (Config_Desc VARCHAR(100),
         Value       VARCHAR(100)
        );
END;
--Backup locally and copy to central location
USE [SQLDBA];
GO
INSERT INTO [Backups].[Backup_Config]
       SELECT 'BackupPath',
              'W:\MSSQL\Backups\'
       UNION ALL
       SELECT 'Compress',
              'Y'
       UNION ALL
       SELECT 'Checksum',
              'Y'
       UNION ALL
       SELECT 'BackupCopyPath',
              '\\backupserver\Live_SQL_Backups3\'+@@SERVERNAME+'\'
       UNION ALL
       SELECT 'LOCALFullBackupRetention',
              '1'
       UNION ALL
       SELECT 'LOCALDiffBackupRetention',
              '1'
       UNION ALL
       SELECT 'LOCALTlogBackupRetention',
              '2'
       UNION ALL
       SELECT 'CENTRALFullBackupRetention',
              '3'
       UNION ALL
       SELECT 'CENTRALDiffBackupRetention',
              '3'
       UNION ALL
       SELECT 'CENTRALTlogBackupRetention',
              '3'
       UNION ALL
       SELECT 'BackupCopyRequired',
              'Y';
GO
--Backup to 1 location
--USE [SQLDBA]
--GO
--INSERT INTO [Backups].[Backup_Config]
--select  'BackupPath','\\backupserver\Live_SQL_Backups2\' + @@SERVERNAME + '\' UNION ALL
--select    'Compress','N' UNION ALL
--select    'Checksum','Y' UNION ALL
--select    'BackupCopyPath','\\backupserver\Live_SQL_Backups2\' + @@SERVERNAME + '\' UNION ALL
--select    'LOCALFullBackupRetention','1' UNION ALL
--select    'LOCALDiffBackupRetention','1' UNION ALL
--select    'LOCALTlogBackupRetention','2' UNION ALL
--select    'CENTRALFullBackupRetention','3' UNION ALL
--select    'CENTRALDiffBackupRetention','3' UNION ALL
--select    'CENTRALTlogBackupRetention','3' UNION ALL
--select      'BackupCopyRequired','N'
--GO



IF
(
    SELECT OBJECT_ID('Backups.Backup_Log')
) IS NULL
    BEGIN
        CREATE TABLE [Backups].[Backup_Log]
        ([ID]                    [INT] IDENTITY(1, 1) NOT NULL,
         [BackupJobName]         [VARCHAR](30) NULL,
         [DatabaseName]          [VARCHAR](128) NULL,
         [StartTime]             [SMALLDATETIME] NULL,
         [Duration]              [VARCHAR](8) NULL,
         [BackupSize]            [BIGINT] NULL,
         [Errors]                [VARCHAR](1000) NULL,
         [FilePath]              [VARCHAR](400) NULL,
         [CopiedToCentralBackup] [BIT] NULL
                                       CONSTRAINT [DF_Backup_Log_CopiedToCentralBackup] DEFAULT((0)),
         [BackupType]            [VARCHAR](4) NULL,
         [RemoveLocal]           [BIT] NULL
                                       CONSTRAINT [DF_Backup_Log_RemoveLocal] DEFAULT((0)),
         [RemoveCentral]         [BIT] NULL
                                       CONSTRAINT [DF_Backup_Log_RemoveCentral] DEFAULT((0)),
         CONSTRAINT [PK_Backup_Log_ID] PRIMARY KEY CLUSTERED([ID] ASC)
         WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
        )
        ON [PRIMARY];
END;
GO
IF
(
    SELECT OBJECT_ID('Backups.Database_Exclusion_List')
) IS NULL
    BEGIN
        CREATE TABLE [Backups].[Database_Exclusion_List]
        (DatabaseName    VARCHAR(128),
         ExclusionReason VARCHAR(2000),
         ExcludedBy      VARCHAR(100)
        );
END;
GO
IF
(
    SELECT OBJECT_ID('Backups.spBackup_V2')
) IS NOT NULL
    BEGIN
        DROP PROCEDURE [Backups].[spBackup_V2];
END;
GO
CREATE PROCEDURE [Backups].[spBackup_V2]
(@DatabaseType NVARCHAR(10),
 @BackupType   NVARCHAR(10),
 @SqlJobName   NVARCHAR(30)
)
AS
     BEGIN
         DECLARE @DatabaseName NVARCHAR(128);
         DECLARE @TimeOfBackup NVARCHAR(17);
         DECLARE @BackupCommand NVARCHAR(1000);
         DECLARE @mindbid INT;
         DECLARE @maxdbid INT;
         DECLARE @backupsize INT;
         DECLARE @filepath VARCHAR(400);
         DECLARE @StartTime DATETIME;
         DECLARE @BackupPath NVARCHAR(300);
         DECLARE @Compress NVARCHAR(1);
         DECLARE @Checksum NVARCHAR(1);
         SET @BackupType = UPPER(@BackupType);
         SET @DatabaseType = UPPER(@DatabaseType);
         SELECT @BackupPath = Value
         FROM [Backups].[Backup_Config]
         WHERE Config_Desc = 'BackupPath';
         SELECT @Compress = Value
         FROM [Backups].[Backup_Config]
         WHERE Config_Desc = 'Compress';
         SELECT @Checksum = Value
         FROM [Backups].[Backup_Config]
         WHERE Config_Desc = 'Checksum';
         IF @DatabaseType NOT IN('SYSTEM', 'USER')
             BEGIN
                 INSERT INTO SQLDBA.backups.Backup_Log
                 (BackupJobName,
                  DatabaseName,
                  StartTime,
                  Errors
                 )
                        SELECT @SqlJobName,
                               'BackupJobStart',
                               GETDATE(),
                               '1.1-Incorrect Database Type Selected - Failed';
                 RAISERROR('Incorrect Database Type Selected', 16, 1);
                 RETURN;
         END;
         IF @BackupType NOT IN('FULL', 'DIFF', 'TLOG')
             BEGIN
                 INSERT INTO SQLDBA.backups.Backup_Log
                 (BackupJobName,
                  DatabaseName,
                  StartTime,
                  Errors
                 )
                        SELECT @SqlJobName,
                               'BackupJobStart',
                               GETDATE(),
                               '1.1-Incorrect Backup Type Selected - Failed';
                 RAISERROR('Incorrect Backup Type Selected', 16, 1);
                 RETURN;
         END;
         IF @BackupType IN('DIFF', 'TLOG')
         AND @DatabaseType = 'SYSTEM'
             BEGIN
                 INSERT INTO SQLDBA.backups.Backup_Log
                 (BackupJobName,
                  DatabaseName,
                  StartTime,
                  Errors
                 )
                        SELECT @SqlJobName,
                               'BackupJobStart',
                               GETDATE(),
                               '1.1-Unable to take Differential or Transaction Log Backups of MASTER Datasbase - Failed';
                 RAISERROR('Unable to take Differential or Transaction Log Backups of MASTER Datasbase', 16, 1);
                 RETURN;
         END;

         --SET SERVER DATABASE_ID FOR EACH DATABASE IN TEMP TABLE
         CREATE TABLE #BackupList
         (ID           INT,
          DatabaseName NVARCHAR(100)
         );
         IF @DatabaseType = 'SYSTEM'
             BEGIN
                 INSERT INTO #BackupList
                        SELECT database_id,
                               name
                        FROM sys.databases
                        WHERE state_desc = 'ONLINE'
                              AND user_access_desc = 'MULTI_USER'
                              AND database_id <= 4
                              AND database_id <> 2;        --exclude tempdb

                 DELETE FROM #BackupList
                 WHERE DatabaseName IN
                 (
                     SELECT DatabaseName
                     FROM SQLDBA.[Backups].[Database_Exclusion_List]
                 );
                 SET @BackupPath = @BackupPath+'\SystemDB\';
                 SELECT *
                 FROM #BackupList;
         END;
             ELSE
         IF @DatabaseType = 'USER'
            AND @BackupType = 'TLOG'
             BEGIN
                 INSERT INTO #BackupList
                        SELECT database_id,
                               name
                        FROM sys.databases
                        WHERE state_desc = 'ONLINE'
                              AND user_access_desc = 'MULTI_USER'
                              AND recovery_model_desc = 'FULL';
                 DELETE FROM #BackupList
                 WHERE DatabaseName IN
                 (
                     SELECT DatabaseName
                     FROM SQLDBA.[Backups].[Database_Exclusion_List]
                 );
                 SET @BackupPath = @BackupPath+'\Tlog\';
                 SELECT *
                 FROM #BackupList;
         END;
             ELSE
         IF @DatabaseType = 'USER'
             BEGIN
                 INSERT INTO #BackupList
                        SELECT database_id,
                               name
                        FROM sys.databases
                        WHERE state_desc = 'ONLINE'
                              AND user_access_desc = 'MULTI_USER'
                              AND database_id > 4;
                 DELETE FROM #BackupList
                 WHERE DatabaseName IN
                 (
                     SELECT DatabaseName
                     FROM SQLDBA.[Backups].[Database_Exclusion_List]
                 );
                 SET @BackupPath = @BackupPath+'\UserDB\';
                 SELECT *
                 FROM #BackupList;
         END;
         SELECT @mindbid = MIN(ID),
                @maxdbid = MAX(ID)
         FROM #BackupList;
         WHILE @mindbid <= @mindbid
             BEGIN
                 SELECT @TimeOfBackup = REPLACE(CAST(CONVERT(NVARCHAR, GETDATE(), 112)+'_'+CONVERT(NVARCHAR, GETDATE(), 108) AS NVARCHAR(17)), ':', '');
                 SELECT @DatabaseName = DatabaseName
                 FROM #BackupList
                 WHERE ID = @mindbid;
                 SET @BackupCommand = '';
                 SELECT @filepath = @BackupPath+UPPER(@DatabaseName)+'_'+@BackupType+'_'+@TimeOfBackup+'.bak';
                 IF @BackupType = 'TLOG'
                     BEGIN
                         SET @BackupCommand = 'BACKUP LOG ['+@DatabaseName+'] ';
                 END;
                     ELSE
                     BEGIN
                         SET @BackupCommand = 'BACKUP DATABASE ['+@DatabaseName+'] ';
                 END;
                 SET @BackupCommand = @BackupCommand+'TO  DISK = N'''+@filepath+''' WITH NOFORMAT ';
                 IF @BackupType = 'DIFF'
                     BEGIN
                         SET @BackupCommand = @BackupCommand+',DIFFERENTIAL ';
                 END;
                 SET @StartTime = GETDATE();
                 SET @BackupCommand = @BackupCommand+CASE
                                                         WHEN @Compress = 'Y'
                                                         THEN ', COMPRESSION '
                                                         ELSE ''
                                                     END+', STATS = 10 '+CASE
                                                                             WHEN @Checksum = 'Y'
                                                                             THEN ', CHECKSUM '
                                                                             ELSE ''
                                                                         END+';';
                 BEGIN TRY
                     SELECT @BackupCommand;
                     EXEC (@BackupCommand);
                     SELECT TOP 1 @backupsize = backup_size / 1024
                     FROM msdb..backupset
                     ORDER BY backup_set_id DESC;
                     INSERT INTO SQLDBA.Backups.Backup_Log
                     (BackupJobName,
                      DatabaseName,
                      StartTime,
                      Duration,
                      Errors,
                      BackupSize,
                      FilePath,
                      BackupType
                     )
                            SELECT @SqlJobName,
                                   @DatabaseName,
                                   @StartTime,
                                   CONVERT(VARCHAR(8), DATEADD(millisecond, DATEDIFF(millisecond, @StartTime, GETDATE()), 0), 8),
                                   'Backup Successful',
                                   @backupsize,
                                   @filepath,
                                   @BackupType;
                 END TRY
                 BEGIN CATCH
                     INSERT INTO SQLDBA.Backups.Backup_Log
                     (BackupJobName,
                      DatabaseName,
                      StartTime,
                      Duration,
                      Errors,
                      BackupType
                     )
                            SELECT @SqlJobName,
                                   @DatabaseName,
                                   @StartTime,
                                   CONVERT(VARCHAR(8), DATEADD(millisecond, DATEDIFF(millisecond, @StartTime, GETDATE()), 0), 8),
                                   'Backup Failed - '+CAST(ERROR_MESSAGE() AS VARCHAR(980)),
                                   @BackupType;
                 END CATCH;
                 SELECT @mindbid = MIN(ID)
                 FROM #BackupList
                 WHERE ID > @mindbid;
             END;
     END;
GO
IF
(
    SELECT OBJECT_ID('Backups.spBackupCopy_V2')
) IS NOT NULL
    BEGIN
        DROP PROCEDURE [Backups].[spBackupCopy_V2];
END;
GO
CREATE PROCEDURE [Backups].[spBackupCopy_V2]
AS
     BEGIN
         IF
         (
             SELECT Value
             FROM [Backups].[Backup_Config]
             WHERE Config_Desc = 'BackupCopyRequired'
         ) = 'Y'
             BEGIN
                 CREATE TABLE #filestocopy
                 (ID       INT,
                  FilePath VARCHAR(200)
                 );
                 INSERT INTO #filestocopy
                        SELECT ID,
                               FilePath
                        FROM [SQLDBA].[Backups].[Backup_Log]
                        WHERE CopiedToCentralBackup = 0
                              AND FilePath IS NOT NULL;
                 SELECT *
                 FROM #filestocopy;
                 DECLARE @minid INT;
                 DECLARE @maxid INT;
                 DECLARE @filepath VARCHAR(200);
                 DECLARE @result INT;
                 DECLARE @command VARCHAR(500);
                 DECLARE @BackupPath VARCHAR(100);
                 DECLARE @copytopath VARCHAR(200);
                 SELECT @copytopath = Value
                 FROM [Backups].[Backup_Config]
                 WHERE Config_Desc = 'BackupCopyPath';
                 SELECT @BackupPath = Value
                 FROM [Backups].[Backup_Config]
                 WHERE Config_Desc = 'BackupPath';
                 SELECT @minid = MIN(ID),
                        @maxid = MAX(ID)
                 FROM #filestocopy;
                 WHILE @minid <= @maxid
                     BEGIN
                         SELECT @filepath = FilePath
                         FROM #filestocopy
                         WHERE ID = @minid;
                         SET @result = '';
                         SET @command = '';
                         SET @command = 'copy "'+@filepath+'" "'+REPLACE(@filepath, @BackupPath, @copytopath)+'" /Y';
                         SELECT @command;
                         EXEC @result = xp_cmdshell
                              @command;
                         IF(@result = 0)
                             BEGIN
                                 UPDATE [SQLDBA].[Backups].[Backup_Log]
                                   SET
                                       [CopiedToCentralBackup] = 1
                                 WHERE ID = @minid;
                         END;
                         SELECT @minid = MIN(ID)
                         FROM #filestocopy
                         WHERE ID > @minid;
         END;
         END;
     END;
GO
IF
(
    SELECT OBJECT_ID('Backups.spBackupCopyArchive_V2')
) IS NOT NULL
    BEGIN
        DROP PROCEDURE [Backups].[spBackupCopyArchive_V2];
END;
GO
CREATE PROCEDURE [Backups].[spBackupCopyArchive_V2]
(@DatabaseType NVARCHAR(10),
 @BackupType   NVARCHAR(10),
 @SqlJobName   NVARCHAR(30)
)
AS
     BEGIN
         DECLARE @copytopath VARCHAR(400);
         DECLARE @sqlcmd VARCHAR(500);
         SET @BackupType = UPPER(@BackupType);
         SET @DatabaseType = UPPER(@DatabaseType);
         IF
         (
             SELECT Value
             FROM [Backups].[Backup_Config]
             WHERE Config_Desc = 'BackupCopyRequired'
         ) = 'Y'
             BEGIN
                 SELECT @copytopath = Value
                 FROM [Backups].[Backup_Config]
                 WHERE Config_Desc = 'BackupCopyPath';
         END;
             ELSE
             BEGIN
                 SELECT @copytopath = Value
                 FROM [Backups].[Backup_Config]
                 WHERE Config_Desc = 'BackupPath';
         END;
         IF @DatabaseType = 'SYSTEM'
             BEGIN
                 SET @copytopath = @copytopath+'SystemDB\';
         END;
             ELSE
         IF @DatabaseType = 'USER'
            AND @BackupType = 'TLOG'
             BEGIN
                 SET @copytopath = @copytopath+'Tlog\';
         END;
             ELSE
         IF @DatabaseType = 'USER'
             BEGIN
                 SET @copytopath = @copytopath+'UserDB\';
         END;
         SET @sqlcmd = 'DIR "'+@copytopath+'" /B /A-D';
         CREATE TABLE #filelist
         (ID             INT IDENTITY(1, 1),
          BackupFilename VARCHAR(200)
         );
         INSERT INTO #filelist
         EXEC xp_cmdshell
              @sqlcmd;
         DELETE FROM #filelist
         WHERE BackupFilename IS NULL;
         DECLARE @backupfilename VARCHAR(100);
         DECLARE @minid INT;
         DECLARE @maxid INT;
         DECLARE @filename VARCHAR(200);
         DECLARE @cmd NVARCHAR(400);
         IF @DatabaseType = 'USER'
            AND @BackupType = 'TLOG'
             BEGIN
                 DELETE FROM #filelist
                 WHERE CAST(STUFF(STUFF(REPLACE(SUBSTRING(BackupFilename, LEN(BackupFilename)-18, 15), '_', ' '), 12, 0, ':'), 15, 0, ':') AS DATETIME) >= CAST(CAST(DATEADD(day, -1, GETDATE()) AS DATE) AS SMALLDATETIME);
                 SELECT @minid = MIN(ID),
                        @maxid = MAX(ID)
                 FROM #filelist;
                 WHILE @minid <= @maxid
                     BEGIN
                         SELECT @filename = BackupFilename
                         FROM #filelist
                         WHERE ID = @minid;
                         SET @cmd = 'move "'+@copytopath+@filename+'" "'+@copytopath+'Archive';
                         EXEC xp_cmdshell
                              @cmd;
                         SELECT @minid = MIN(ID)
                         FROM #filelist
                         WHERE ID > @minid;
         END;
         END;
             ELSE
             BEGIN
                 SELECT @minid = MIN(ID),
                        @maxid = MAX(ID)
                 FROM #filelist;
                 WHILE @minid <= @maxid
                     BEGIN
                         SELECT @filename = BackupFilename
                         FROM #filelist
                         WHERE ID = @minid;
                         SET @cmd = 'move "'+@copytopath+@filename+'" "'+@copytopath+'Archive';
                         EXEC xp_cmdshell
                              @cmd;
                         SELECT @minid = MIN(ID)
                         FROM #filelist
                         WHERE ID > @minid;
         END;
         END;
     END;
GO
IF
(
    SELECT OBJECT_ID('Backups.spBackupRetentionDelete_V2')
) IS NOT NULL
    BEGIN
        DROP PROCEDURE [Backups].[spBackupRetentionDelete_V2];
END;
GO
CREATE PROCEDURE [Backups].[spBackupRetentionDelete_V2]
AS
     BEGIN
         DECLARE @LOCALFullBackupRetention INT;
         DECLARE @LOCALDiffBackupRetention INT;
         DECLARE @LOCALTlogBackupRetention INT;
         DECLARE @CENTRALFullBackupRetention INT;
         DECLARE @CENTRALDiffBackupRetention INT;
         DECLARE @CENTRALTlogBackupRetention INT;
         DECLARE @minid INT;
         DECLARE @maxid INT;
         DECLARE @filepath VARCHAR(200);
         DECLARE @result INT;
         DECLARE @command VARCHAR(500);
         DECLARE @backuppath VARCHAR(100);
         DECLARE @copytopath VARCHAR(200);
         IF
         (
             SELECT Value
             FROM [Backups].[Backup_Config]
             WHERE Config_Desc = 'BackupCopyRequired'
         ) = 'Y'
             BEGIN
                 SELECT @copytopath = Value
                 FROM [Backups].[Backup_Config]
                 WHERE Config_Desc = 'BackupCopyPath';
         END;
             ELSE
             BEGIN
                 SELECT @copytopath = Value
                 FROM [Backups].[Backup_Config]
                 WHERE Config_Desc = 'BackupPath';
         END;
         SELECT @backuppath = Value
         FROM [Backups].[Backup_Config]
         WHERE Config_Desc = 'BackupPath';
         SELECT @LOCALFullBackupRetention = Value
         FROM [Backups].[Backup_Config]
         WHERE Config_Desc = 'LOCALFullBackupRetention';
         SELECT @LOCALDiffBackupRetention = Value
         FROM [Backups].[Backup_Config]
         WHERE Config_Desc = 'LOCALDiffBackupRetention';
         SELECT @LOCALTlogBackupRetention = Value
         FROM [Backups].[Backup_Config]
         WHERE Config_Desc = 'LOCALTlogBackupRetention';
         SELECT @CENTRALFullBackupRetention = Value
         FROM [Backups].[Backup_Config]
         WHERE Config_Desc = 'CENTRALFullBackupRetention';
         SELECT @CENTRALDiffBackupRetention = Value
         FROM [Backups].[Backup_Config]
         WHERE Config_Desc = 'CENTRALDiffBackupRetention';
         SELECT @CENTRALTlogBackupRetention = Value
         FROM [Backups].[Backup_Config]
         WHERE Config_Desc = 'CENTRALTlogBackupRetention';
         IF
         (
             SELECT Value
             FROM [Backups].[Backup_Config]
             WHERE Config_Desc = 'BackupCopyRequired'
         ) = 'Y'
             BEGIN
                 --DELETE LOCAL BACKUP FILES
                 CREATE TABLE #LOCALfilestodelete
                 (ID       INT,
                  FilePath VARCHAR(200)
                 );
                 INSERT INTO #LOCALfilestodelete
                        SELECT ID,
                               FilePath
                        FROM [SQLDBA].[Backups].[Backup_Log]
                        WHERE [RemoveLocal] = 0
                              AND FilePath IS NOT NULL
                              AND ((StartTime < DATEADD(day, -@LOCALFullBackupRetention, GETDATE())
                                    AND BackupType LIKE 'FULL')
                                   OR (StartTime < DATEADD(day, -@LOCALDiffBackupRetention, GETDATE())
                                       AND BackupType LIKE 'DIFF')
                                   OR (StartTime < DATEADD(day, -@LOCALTlogBackupRetention, GETDATE())
                                       AND BackupType LIKE 'TLOG'));
                 SELECT @minid = MIN(ID),
                        @maxid = MAX(ID)
                 FROM #LOCALfilestodelete;
                 WHILE @minid <= @maxid
                     BEGIN
                         SELECT @filepath = FilePath
                         FROM #LOCALfilestodelete
                         WHERE ID = @minid;
                         SET @result = '';
                         SET @command = '';
                         SET @command = 'del "'+@filepath+'" /F /Q';
                         EXEC @result = xp_cmdshell
                              @command;
                         IF(@result = 0)
                             BEGIN
                                 UPDATE [SQLDBA].[Backups].[Backup_Log]
                                   SET
                                       [RemoveLocal] = 1
                                 WHERE ID = @minid;
                         END;
                         SELECT @minid = MIN(ID)
                         FROM #LOCALfilestodelete
                         WHERE ID > @minid;
         END;
         END;

         --DELETE CENTRAL BACKUP FILES
         CREATE TABLE #CENTRALfilestodelete
         (ID       INT,
          FilePath VARCHAR(200)
         );
         INSERT INTO #CENTRALfilestodelete
                SELECT ID,
                       FilePath
                FROM [SQLDBA].[Backups].[Backup_Log]
                WHERE [RemoveCentral] = 0
                      AND (StartTime < DATEADD(day, -@CENTRALFullBackupRetention, GETDATE())
                           AND BackupType LIKE 'FULL')
                      OR (StartTime < DATEADD(day, -@CENTRALDiffBackupRetention, GETDATE())
                          AND BackupType LIKE 'DIFF')
                      OR (StartTime < DATEADD(day, -@CENTRALTlogBackupRetention, GETDATE())
                          AND BackupType LIKE 'TLOG');
         SET @minid = '';
         SET @maxid = '';
         SELECT @minid = MIN(ID),
                @maxid = MAX(ID)
         FROM #CENTRALfilestodelete;
         WHILE @minid <= @maxid
             BEGIN
                 SELECT @filepath = FilePath
                 FROM #CENTRALfilestodelete
                 WHERE ID = @minid;
                 SET @result = '';
                 SET @command = '';
                 SET @command = 'del "'+REPLACE(REPLACE(REPLACE(REPLACE(@filepath, @backuppath, @copytopath), 'UserDB\', 'UserDB\Archive\'), 'SystemDB\', 'SystemDB\Archive\'), 'Tlog\', 'Tlog\Archive\')+'" /F /Q';
                 EXEC @result = xp_cmdshell
                      @command;
                 IF(@result = 0)
                     BEGIN
                         UPDATE [SQLDBA].[Backups].[Backup_Log]
                           SET
                               [RemoveCentral] = 1
                         WHERE ID = @minid;
                 END;
                 SELECT @minid = MIN(ID)
                 FROM #CENTRALfilestodelete
                 WHERE ID > @minid;
             END;
     END;
GO
USE msdb;
IF EXISTS
(
    SELECT *
    FROM msdb.dbo.sysjobs
    WHERE name = 'DB_Backups - CleanUp'
)
    BEGIN
        EXEC msdb.dbo.sp_delete_job
             @job_name = N'DB_Backups - CleanUp',
             @delete_unused_schedule = 1;
END;
BEGIN TRANSACTION;
DECLARE @ReturnCode INT;
SELECT @ReturnCode = 0;

/****** Object:  JobCategory [DBA_Maintenance]    Script Date: 12/01/2015 11:09:59 ******/

IF NOT EXISTS
(
    SELECT name
    FROM msdb.dbo.syscategories
    WHERE name = N'DBA_Maintenance'
          AND category_class = 1
)
    BEGIN
        EXEC @ReturnCode = msdb.dbo.sp_add_category
             @class = N'JOB',
             @type = N'LOCAL',
             @name = N'DBA_Maintenance';
        IF(@@ERROR <> 0
           OR @ReturnCode <> 0)
            GOTO QuitWithRollback;
END;
DECLARE @jobId BINARY(16);
EXEC @ReturnCode = msdb.dbo.sp_add_job
     @job_name = N'DB_Backups - CleanUp',
     @enabled = 1,
     @notify_level_eventlog = 0,
     @notify_level_email = 0,
     @notify_level_netsend = 0,
     @notify_level_page = 0,
     @delete_level = 0,
     @description = N'Delete backups older than x',
     @category_name = N'DBA_Maintenance',
     @owner_login_name = N'sa',
     @job_id = @jobId OUTPUT;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;

/****** Object:  Step [Clean Up Backups]    Script Date: 12/01/2015 11:09:59 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
     @job_id = @jobId,
     @step_name = N'Clean Up Backups',
     @step_id = 1,
     @cmdexec_success_code = 0,
     @on_success_action = 1,
     @on_success_step_id = 0,
     @on_fail_action = 2,
     @on_fail_step_id = 0,
     @retry_attempts = 0,
     @retry_interval = 0,
     @os_run_priority = 0,
     @subsystem = N'TSQL',
     @command = N'
EXEC [SQLDBA].[Backups].[spBackupRetentionDelete_V2]
',
     @database_name = N'SQLDBA',
     @flags = 8;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_update_job
     @job_id = @jobId,
     @start_step_id = 1;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule
     @job_id = @jobId,
     @name = N'daily 19:00',
     @enabled = 1,
     @freq_type = 4,
     @freq_interval = 1,
     @freq_subday_type = 1,
     @freq_subday_interval = 0,
     @freq_relative_interval = 0,
     @freq_recurrence_factor = 0,
     @active_start_date = 20131018,
     @active_end_date = 99991231,
     @active_start_time = 190000,
     @active_end_time = 235959;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
     @job_id = @jobId,
     @server_name = N'(local)';
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
COMMIT TRANSACTION;
GOTO EndSave;
QuitWithRollback:
IF(@@TRANCOUNT > 0)
    ROLLBACK TRANSACTION;
EndSave:
GO
USE msdb;
IF EXISTS
(
    SELECT *
    FROM msdb.dbo.sysjobs
    WHERE name = 'DB_Backups - FULL - SYSTEM_DBs'
)
    BEGIN
        EXEC msdb.dbo.sp_delete_job
             @job_name = N'DB_Backups - FULL - SYSTEM_DBs',
             @delete_unused_schedule = 1;
END;
BEGIN TRANSACTION;
DECLARE @ReturnCode INT;
SELECT @ReturnCode = 0;
DECLARE @jobId BINARY(16);
EXEC @ReturnCode = msdb.dbo.sp_add_job
     @job_name = N'DB_Backups - FULL - SYSTEM_DBs',
     @enabled = 1,
     @notify_level_eventlog = 0,
     @notify_level_email = 0,
     @notify_level_netsend = 0,
     @notify_level_page = 0,
     @delete_level = 0,
     @description = N'Job Loops round all specified Databases and backs up accourding to backup type.',
     @category_name = N'DBA_Maintenance',
     @owner_login_name = N'sa',
     @job_id = @jobId OUTPUT;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;

/****** Object:  Step [Move to Archive Folder]    Script Date: 05/02/2015 15:10:16 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
     @job_id = @jobId,
     @step_name = N'Move to Archive Folder',
     @step_id = 1,
     @cmdexec_success_code = 0,
     @on_success_action = 3,
     @on_success_step_id = 0,
     @on_fail_action = 2,
     @on_fail_step_id = 0,
     @retry_attempts = 0,
     @retry_interval = 0,
     @os_run_priority = 0,
     @subsystem = N'TSQL',
     @command = N'
declare @DatabaseType Nvarchar(10)
declare @BackupType Nvarchar(10)
declare @SqlJobName Nvarchar(30)

set @SqlJobName = ''DB_Backups - FULL - SYSTEM_DBs''
SET @DatabaseType = ''SYSTEM''
    --Values allowed    --''SYSTEM'' System database backups (Master,Model,MSDB)
            --''USER'' User Database backups
SET @BackupType = ''FULL''
    --Values allowed    --''FULL'' full database backup
            --''DIFF'' differential backup
            --''TLOG'' transaction log backup

exec [SQLDBA].[Backups].[spBackupCopyArchive_V2]
        @DatabaseType = @DatabaseType
        ,@BackupType = @BackupType
        ,@SqlJobName = @SqlJobName

',
     @database_name = N'SQLDBA',
     @flags = 8;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;

/****** Object:  Step [Backup SYSTEM DBs]    Script Date: 05/02/2015 15:10:16 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
     @job_id = @jobId,
     @step_name = N'Backup SYSTEM DBs',
     @step_id = 2,
     @cmdexec_success_code = 0,
     @on_success_action = 3,
     @on_success_step_id = 0,
     @on_fail_action = 2,
     @on_fail_step_id = 0,
     @retry_attempts = 0,
     @retry_interval = 0,
     @os_run_priority = 0,
     @subsystem = N'TSQL',
     @command = N'
declare @DatabaseType Nvarchar(10)
declare @BackupType Nvarchar(10)
declare @SqlJobName Nvarchar(30)

set @SqlJobName = ''DB_Backups - FULL - SYSTEM_DBs''
SET @DatabaseType = ''SYSTEM''
    --Values allowed    --''SYSTEM'' System database backups (Master,Model,MSDB)
            --''USER'' User Database backups
SET @BackupType = ''FULL''
    --Values allowed    --''FULL'' full database backup
            --''DIFF'' differential backup
            --''TLOG'' transaction log backup

EXEC SQLDBA.Backups.spBackup_V2
        @DatabaseType = @DatabaseType
        ,@BackupType = @BackupType
        ,@SqlJobName = @SqlJobName
',
     @database_name = N'SQLDBA',
     @flags = 8;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;

/****** Object:  Step [Copy to Central Location]    Script Date: 05/02/2015 15:10:16 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
     @job_id = @jobId,
     @step_name = N'Copy to Central Location',
     @step_id = 3,
     @cmdexec_success_code = 0,
     @on_success_action = 1,
     @on_success_step_id = 0,
     @on_fail_action = 2,
     @on_fail_step_id = 0,
     @retry_attempts = 0,
     @retry_interval = 0,
     @os_run_priority = 0,
     @subsystem = N'TSQL',
     @command = N'
EXEC [SQLDBA].[Backups].[spBackupCopy_V2]

',
     @database_name = N'SQLDBA',
     @flags = 8;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_update_job
     @job_id = @jobId,
     @start_step_id = 1;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule
     @job_id = @jobId,
     @name = N'Daily 22.00',
     @enabled = 1,
     @freq_type = 4,
     @freq_interval = 1,
     @freq_subday_type = 1,
     @freq_subday_interval = 0,
     @freq_relative_interval = 0,
     @freq_recurrence_factor = 0,
     @active_start_date = 20131014,
     @active_end_date = 99991231,
     @active_start_time = 220000,
     @active_end_time = 235959;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
     @job_id = @jobId,
     @server_name = N'(local)';
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
COMMIT TRANSACTION;
GOTO EndSave;
QuitWithRollback:
IF(@@TRANCOUNT > 0)
    ROLLBACK TRANSACTION;
EndSave:
GO
USE msdb;
IF EXISTS
(
    SELECT *
    FROM msdb.dbo.sysjobs
    WHERE name = 'DB_Backups - FULL - USER_DBs'
)
    BEGIN
        EXEC msdb.dbo.sp_delete_job
             @job_name = N'DB_Backups - FULL - USER_DBs',
             @delete_unused_schedule = 1;
END;
BEGIN TRANSACTION;
DECLARE @ReturnCode INT;
SELECT @ReturnCode = 0;
DECLARE @jobId BINARY(16);
EXEC @ReturnCode = msdb.dbo.sp_add_job
     @job_name = N'DB_Backups - FULL - USER_DBs',
     @enabled = 1,
     @notify_level_eventlog = 0,
     @notify_level_email = 0,
     @notify_level_netsend = 0,
     @notify_level_page = 0,
     @delete_level = 0,
     @description = N'Job Loops round all specified Databases and backs up accourding to backup type.',
     @category_name = N'DBA_Maintenance',
     @owner_login_name = N'sa',
     @job_id = @jobId OUTPUT;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;

/****** Object:  Step [Move to Archive Folder]    Script Date: 05/02/2015 15:11:13 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
     @job_id = @jobId,
     @step_name = N'Move to Archive Folder',
     @step_id = 1,
     @cmdexec_success_code = 0,
     @on_success_action = 3,
     @on_success_step_id = 0,
     @on_fail_action = 2,
     @on_fail_step_id = 0,
     @retry_attempts = 0,
     @retry_interval = 0,
     @os_run_priority = 0,
     @subsystem = N'TSQL',
     @command = N'
declare @DatabaseType Nvarchar(10)
declare @BackupType Nvarchar(10)
declare @SqlJobName Nvarchar(30)

set @SqlJobName = ''DB_Backups - FULL - USER_DBs''
SET @DatabaseType = ''USER''
    --Values allowed    --''SYSTEM'' System database backups (Master,Model,MSDB)
            --''USER'' User Database backups
SET @BackupType = ''FULL''
    --Values allowed    --''FULL'' full database backup
            --''DIFF'' differential backup
            --''TLOG'' transaction log backup

exec [SQLDBA].[Backups].[spBackupCopyArchive_V2]
        @DatabaseType = @DatabaseType
        ,@BackupType = @BackupType
        ,@SqlJobName = @SqlJobName
',
     @database_name = N'SQLDBA',
     @flags = 8;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;

/****** Object:  Step [Backup USER DBs]    Script Date: 05/02/2015 15:11:13 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
     @job_id = @jobId,
     @step_name = N'Backup USER DBs',
     @step_id = 2,
     @cmdexec_success_code = 0,
     @on_success_action = 3,
     @on_success_step_id = 0,
     @on_fail_action = 2,
     @on_fail_step_id = 0,
     @retry_attempts = 0,
     @retry_interval = 0,
     @os_run_priority = 0,
     @subsystem = N'TSQL',
     @command = N'
declare @DatabaseType Nvarchar(10)
declare @BackupType Nvarchar(10)
declare @SqlJobName Nvarchar(30)

set @SqlJobName = ''DB_Backups - FULL - USER_DBs''
SET @DatabaseType = ''USER''
    --Values allowed    --''SYSTEM'' System database backups (Master,Model,MSDB)
            --''USER'' User Database backups
SET @BackupType = ''FULL''
    --Values allowed    --''FULL'' full database backup
            --''DIFF'' differential backup
            --''TLOG'' transaction log backup

EXEC SQLDBA.Backups.spBackup_V2
        @DatabaseType = @DatabaseType
        ,@BackupType = @BackupType
        ,@SqlJobName = @SqlJobName
',
     @database_name = N'SQLDBA',
     @flags = 8;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;

/****** Object:  Step [Copy to Central Location]    Script Date: 05/02/2015 15:11:13 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
     @job_id = @jobId,
     @step_name = N'Copy to Central Location',
     @step_id = 3,
     @cmdexec_success_code = 0,
     @on_success_action = 1,
     @on_success_step_id = 0,
     @on_fail_action = 2,
     @on_fail_step_id = 0,
     @retry_attempts = 0,
     @retry_interval = 0,
     @os_run_priority = 0,
     @subsystem = N'TSQL',
     @command = N'
EXEC [SQLDBA].[Backups].[spBackupCopy_V2]
',
     @database_name = N'SQLDBA',
     @flags = 8;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_update_job
     @job_id = @jobId,
     @start_step_id = 1;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule
     @job_id = @jobId,
     @name = N'Daily 19.00',
     @enabled = 1,
     @freq_type = 4,
     @freq_interval = 1,
     @freq_subday_type = 1,
     @freq_subday_interval = 0,
     @freq_relative_interval = 0,
     @freq_recurrence_factor = 0,
     @active_start_date = 20140914,
     @active_end_date = 99991231,
     @active_start_time = 190000,
     @active_end_time = 235959;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
     @job_id = @jobId,
     @server_name = N'(local)';
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
COMMIT TRANSACTION;
GOTO EndSave;
QuitWithRollback:
IF(@@TRANCOUNT > 0)
    ROLLBACK TRANSACTION;
EndSave:
GO
USE msdb;
IF EXISTS
(
    SELECT *
    FROM msdb.dbo.sysjobs
    WHERE name = 'DB_Backups - DIFFERENTIAL - USER_DBs'
)
    BEGIN
        EXEC msdb.dbo.sp_delete_job
             @job_name = N'DB_Backups - DIFFERENTIAL - USER_DBs',
             @delete_unused_schedule = 1;
END;
BEGIN TRANSACTION;
DECLARE @ReturnCode INT;
SELECT @ReturnCode = 0;
DECLARE @jobId BINARY(16);
EXEC @ReturnCode = msdb.dbo.sp_add_job
     @job_name = N'DB_Backups - DIFFERENTIAL - USER_DBs',
     @enabled = 0,
     @notify_level_eventlog = 0,
     @notify_level_email = 0,
     @notify_level_netsend = 0,
     @notify_level_page = 0,
     @delete_level = 0,
     @description = N'Job Loops round all specified Databases and backs up accourding to backup type.',
     @category_name = N'DBA_Maintenance',
     @owner_login_name = N'sa',
     @job_id = @jobId OUTPUT;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;

/****** Object:  Step [Move to Archive Folder]    Script Date: 05/02/2015 15:08:50 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
     @job_id = @jobId,
     @step_name = N'Move to Archive Folder',
     @step_id = 1,
     @cmdexec_success_code = 0,
     @on_success_action = 3,
     @on_success_step_id = 0,
     @on_fail_action = 2,
     @on_fail_step_id = 0,
     @retry_attempts = 0,
     @retry_interval = 0,
     @os_run_priority = 0,
     @subsystem = N'TSQL',
     @command = N'
declare @DatabaseType Nvarchar(10)
declare @BackupType Nvarchar(10)
declare @SqlJobName Nvarchar(30)

set @SqlJobName = ''DB_Backups - FULL - USER_DBs''
SET @DatabaseType = ''USER''
    --Values allowed    --''SYSTEM'' System database backups (Master,Model,MSDB)
            --''USER'' User Database backups
SET @BackupType = ''DIFF''
    --Values allowed    --''FULL'' full database backup
            --''DIFF'' differential backup
            --''TLOG'' transaction log backup

exec [SQLDBA].[Backups].[spBackupCopyArchive_V2]
        @DatabaseType = @DatabaseType
        ,@BackupType = @BackupType
        ,@SqlJobName = @SqlJobName
',
     @database_name = N'SQLDBA',
     @flags = 8;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;

/****** Object:  Step [Backup DIFFERENTIAL USER DB's]    Script Date: 05/02/2015 15:08:50 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
     @job_id = @jobId,
     @step_name = N'Backup DIFFERENTIAL USER DB''s',
     @step_id = 2,
     @cmdexec_success_code = 0,
     @on_success_action = 3,
     @on_success_step_id = 0,
     @on_fail_action = 2,
     @on_fail_step_id = 0,
     @retry_attempts = 0,
     @retry_interval = 0,
     @os_run_priority = 0,
     @subsystem = N'TSQL',
     @command = N'
declare @DatabaseType Nvarchar(10)
declare @BackupType Nvarchar(10)
declare @SqlJobName Nvarchar(30)

set @SqlJobName = ''DB_Backups - FULL - USER_DBs''
SET @DatabaseType = ''USER''
    --Values allowed    --''SYSTEM'' System database backups (Master,Model,MSDB)
            --''USER'' User Database backups
SET @BackupType = ''DIFF''
    --Values allowed    --''FULL'' full database backup
            --''DIFF'' differential backup
            --''TLOG'' transaction log backup

EXEC SQLDBA.Backups.spBackup_V2
        @DatabaseType = @DatabaseType
        ,@BackupType = @BackupType
        ,@SqlJobName = @SqlJobName

',
     @database_name = N'SQLDBA',
     @flags = 8;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;

/****** Object:  Step [Copy to Central Location]    Script Date: 05/02/2015 15:08:50 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
     @job_id = @jobId,
     @step_name = N'Copy to Central Location',
     @step_id = 3,
     @cmdexec_success_code = 0,
     @on_success_action = 1,
     @on_success_step_id = 0,
     @on_fail_action = 2,
     @on_fail_step_id = 0,
     @retry_attempts = 0,
     @retry_interval = 0,
     @os_run_priority = 0,
     @subsystem = N'TSQL',
     @command = N'
EXEC [SQLDBA].[Backups].[spBackupCopy_V2]
',
     @database_name = N'SQLDBA',
     @flags = 8;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_update_job
     @job_id = @jobId,
     @start_step_id = 1;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule
     @job_id = @jobId,
     @name = N'Daily 01.00',
     @enabled = 0,
     @freq_type = 4,
     @freq_interval = 1,
     @freq_subday_type = 1,
     @freq_subday_interval = 0,
     @freq_relative_interval = 0,
     @freq_recurrence_factor = 0,
     @active_start_date = 20131014,
     @active_end_date = 99991231,
     @active_start_time = 10000,
     @active_end_time = 235959;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
     @job_id = @jobId,
     @server_name = N'(local)';
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
COMMIT TRANSACTION;
GOTO EndSave;
QuitWithRollback:
IF(@@TRANCOUNT > 0)
    ROLLBACK TRANSACTION;
EndSave:
GO
USE msdb;
IF EXISTS
(
    SELECT *
    FROM msdb.dbo.sysjobs
    WHERE name = 'DB_Backups - TLOG - USER_DBs'
)
    BEGIN
        EXEC msdb.dbo.sp_delete_job
             @job_name = N'DB_Backups - TLOG - USER_DBs',
             @delete_unused_schedule = 1;
END;
BEGIN TRANSACTION;
DECLARE @ReturnCode INT;
SELECT @ReturnCode = 0;
DECLARE @jobId BINARY(16);
EXEC @ReturnCode = msdb.dbo.sp_add_job
     @job_name = N'DB_Backups - TLOG - USER_DBs',
     @enabled = 1,
     @notify_level_eventlog = 0,
     @notify_level_email = 0,
     @notify_level_netsend = 0,
     @notify_level_page = 0,
     @delete_level = 0,
     @description = N'Job Loops round all specified Databases and backs up accourding to backup type.',
     @category_name = N'DBA_Maintenance',
     @owner_login_name = N'sa',
     @job_id = @jobId OUTPUT;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;

/****** Object:  Step [Move Backups to Archive]    Script Date: 05/02/2015 15:11:54 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
     @job_id = @jobId,
     @step_name = N'Move Backups to Archive',
     @step_id = 1,
     @cmdexec_success_code = 0,
     @on_success_action = 3,
     @on_success_step_id = 0,
     @on_fail_action = 2,
     @on_fail_step_id = 0,
     @retry_attempts = 0,
     @retry_interval = 0,
     @os_run_priority = 0,
     @subsystem = N'TSQL',
     @command = N'
declare @DatabaseType Nvarchar(10)
declare @BackupType Nvarchar(10)
declare @SqlJobName Nvarchar(30)

set @SqlJobName = ''DB_Backups - TLOG - USER_DBs''
SET @DatabaseType = ''USER''
    --Values allowed    --''SYSTEM'' System database backups (Master,Model,MSDB)
            --''USER'' User Database backups
SET @BackupType = ''TLOG''
    --Values allowed    --''FULL'' full database backup
            --''DIFF'' differential backup
            --''TLOG'' transaction log backup


if  getdate() between
cast(cast(getdate() as varchar(11)) + '' 20:06'' as smalldatetime)  and
cast(cast(getdate() as varchar(11)) + '' 20:40'' as smalldatetime)
begin

exec [SQLDBA].[Backups].[spBackupCopyArchive_V2]
        @DatabaseType = @DatabaseType
        ,@BackupType = @BackupType
        ,@SqlJobName = @SqlJobName


end
',
     @database_name = N'SQLDBA',
     @flags = 8;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;

/****** Object:  Step [Backup TLOG USER DBs]    Script Date: 05/02/2015 15:11:54 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
     @job_id = @jobId,
     @step_name = N'Backup TLOG USER DBs',
     @step_id = 2,
     @cmdexec_success_code = 0,
     @on_success_action = 3,
     @on_success_step_id = 0,
     @on_fail_action = 2,
     @on_fail_step_id = 0,
     @retry_attempts = 0,
     @retry_interval = 0,
     @os_run_priority = 0,
     @subsystem = N'TSQL',
     @command = N'declare @DatabaseType Nvarchar(10)
declare @BackupType Nvarchar(10)
declare @SqlJobName Nvarchar(30)

set @SqlJobName = ''DB_Backups - TLOG - USER_DBs''
SET @DatabaseType = ''USER''
    --Values allowed    --''SYSTEM'' System database backups (Master,Model,MSDB)
            --''USER'' User Database backups
SET @BackupType = ''TLOG''
    --Values allowed    --''FULL'' full database backup
            --''DIFF'' differential backup
            --''TLOG'' transaction log backup

EXEC SQLDBA.Backups.spBackup_V2
        @DatabaseType = @DatabaseType
        ,@BackupType = @BackupType
        ,@SqlJobName = @SqlJobName
',
     @database_name = N'SQLDBA',
     @flags = 8;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;

/****** Object:  Step [Copy to Central Location]    Script Date: 05/02/2015 15:11:54 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
     @job_id = @jobId,
     @step_name = N'Copy to Central Location',
     @step_id = 3,
     @cmdexec_success_code = 0,
     @on_success_action = 1,
     @on_success_step_id = 0,
     @on_fail_action = 2,
     @on_fail_step_id = 0,
     @retry_attempts = 0,
     @retry_interval = 0,
     @os_run_priority = 0,
     @subsystem = N'TSQL',
     @command = N'
EXEC [SQLDBA].[Backups].[spBackupCopy_V2]
',
     @database_name = N'SQLDBA',
     @flags = 8;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_update_job
     @job_id = @jobId,
     @start_step_id = 1;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule
     @job_id = @jobId,
     @name = N'Every 30mins',
     @enabled = 1,
     @freq_type = 4,
     @freq_interval = 1,
     @freq_subday_type = 4,
     @freq_subday_interval = 30,
     @freq_relative_interval = 0,
     @freq_recurrence_factor = 0,
     @active_start_date = 20140304,
     @active_end_date = 99991231,
     @active_start_time = 1500,
     @active_end_time = 235000;
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
     @job_id = @jobId,
     @server_name = N'(local)';
IF(@@ERROR <> 0
   OR @ReturnCode <> 0)
    GOTO QuitWithRollback;
COMMIT TRANSACTION;
GOTO EndSave;
QuitWithRollback:
IF(@@TRANCOUNT > 0)
    ROLLBACK TRANSACTION;
EndSave:
GO
From my SQL Server DBA handbook.
Setup
Clive Roberts

Before you create backup objects and jobs

The script
