Create_Backup_Objects_and_Jobs

/*
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 can’t
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’, ‘\\share\Live_SQL_Backups\’+@@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’,’\\mascodpm001\Live_SQL_Backups2\’ + @@SERVERNAME + ‘\’ UNION ALL
–select ‘Compress’,’N’ UNION ALL
–select ‘Checksum’,’Y’ UNION ALL
–select ‘BackupCopyPath’,’\\mascodpm001\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