Technical Article

Comprehensive Backup Script

,

The script contains 3 procs, 2 UDF's (one each for 2000 and 2005), and 5 jobs (created as disabled). To execute this script, you must add the Backup Folder and Log Folder values to the top of the script, else the jobs wont be created. The procs (and UDF) are created in master, hence the use of sp_ as the prefix.

This complete script is 1800 + lines long ...

The procs are:

(1) sp_PathExists - this will verify and create the specified folder path

(2) fn_DatabaseDetails - this will get the DB metadata for the server, two UDF's are included, one for 2000 and one for 2005, implemented as dynamic SQL

(3) sp_DatabaseBackup - this does the work.

(4) sp_DeleteFiles - this proc does the clean-up work, to delete old backups.

(5) Jobs - 5 jobs are created -

File Cleanup

System DB - Full Backups

User DB - Differential Backups

User DB - Full Backups

User DB - Transaction Log Backup

The jobs are spec'd to our generic / basic backup strategy (weekly Fulls (Friday, @ 6pm), nightly Differentials (nightly @ 6pm, except Friday), daily T-Log @12:00am. Obviously, this is customized per server as required.

sp_DatabaseBackup has intelligence built in - A Full backup will be run if a Differential backup was requested, and there is no prior Full, T-Log backups won't be attempted on any DB in Simple recovery mode, etc

.

The objects (procs and UDF's) are well commented as to the parameters for each, but if you run the whole script, you can check out the jobs that are created for examples on how to run this process.

Enjoy ... and if you find any errors, or improvements, please let me know (and the "Comprehensive" in the title is a little tongue-in-cheek, there are lots more options that could be included - LiteSpeed, Marked Transactions to name but two ...)

CREATE TABLE #Folders (BackupFolder VARCHAR(256),
 LogFolder VARCHAR(256))

-- Enter your Folder paths into these values, if both these values
-- are not entered, the jobs will not be created.
-- The Procs and UDF will be created regardless of the presence of
-- these values.
INSERT #Folders VALUES (
 '', -- Backup Folder
 '') -- LOG Folder

GO

-- (1) sp_PathExists

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_PathExists] Script Date: 10/09/2007 08:56:25 ******/IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_PathExists' AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_PathExists]

GO
/****** Object: UserDefinedFunction [dbo].[fn_ParsePath] Script Date: 10/08/2007 08:48:31 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_PathExists] (
 @PathValue AS VARCHAR(256),
 @CreateFolder AS BIT = 1)

AS
-- --------------------------------------------------------------------------------------------------
-- Procedure : [sp_PathExists]
-- Description : To verify if the given path exists,
-- Create the path if necessary
-- Parameters PathValue The folder path to verify,
-- REQUIRED.
-- CreateFolder Create the folder if it doesnt exist.
-- OPTIONAL - defaults to 1 (Yes).
--
-- Modification Log
-- When Who Description
-- 10/09/2007 Simon Facer Original Version
-- --------------------------------------------------------------------------------------------------

BEGIN

 SET NOCOUNT ON 

 DECLARE @FolderPath VARCHAR(256)
 DECLARE @PathComponent VARCHAR(64)
 DECLARE @Idx INT
 DECLARE @PathLen INT
 DECLARE @OS_Cmd VARCHAR(1024)
 DECLARE @PathExists INT

 CREATE TABLE #PathComponents (
 FolderPath VARCHAR(256),
 PathComponent VARCHAR(64))

 CREATE TABLE #OS_CMD_Results (
 OS_Output VARCHAR(1024) )


 -- ******************************************************************************************
 -- Set the PathExists value to 1, it will be reset to 0 as necessary
 SELECT @PathExists = 1
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Check if the path exists, if so, exit the proc
 SELECT @OS_Cmd = 'DIR "' + @PathValue + '" '

 INSERT #OS_CMD_Results 
 EXEC xp_cmdshell @OS_Cmd

 IF NOT EXISTS (SELECT *
 FROM #OS_CMD_Results
 WHERE OS_Output LIKE '%File Not Found%' 
 OR OS_Output LIKE '%The system cannot find the path specified.%'
 OR OS_Output LIKE '%The system cannot find the file specified.%')
 BEGIN
 SELECT @PathExists = 1
 GOTO TheEnd
 END
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Parse the path into it's components
 SELECT @Idx = 0

 SELECT @PathValue = LTRIM(RTRIM(@PathValue))

 WHILE CHARINDEX('\', @PathValue, (@Idx + 1)) > 0
 BEGIN
 SELECT @Idx = CHARINDEX('\', @PathValue, (@Idx + 1)) 
 SELECT @FolderPath = SUBSTRING(@PathValue, 1, (@Idx - 1))
 SELECT @PathComponent = SUBSTRING(@FolderPath, (@PathLen + 2), 999)
 SELECT @PathLen = LEN(@FolderPath)
 INSERT #PathComponents
 VALUES(@FolderPath, @PathComponent)
 END

 SELECT @PathComponent = SUBSTRING(@PathValue, (@PathLen + 2), 999)
 INSERT #PathComponents
 VALUES (@PathValue, @PathComponent)
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Declare the cursor to loop through the path components
 DECLARE csrPath CURSOR FOR
 SELECT FolderPath,
 PathComponent
 FROM #PathComponents
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Open and retrieve the first values from the cursor
 OPEN csrPath
 FETCH NEXT FROM csrPath
 INTO @FolderPath,
 @PathComponent
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Loop through the Path components, and verify the existence of each
 WHILE @@FETCH_STATUS = 0
 BEGIN

 -- ******************************************************************************************
 -- If the path component doesnt exist,
 -- (1) Create it if @CreateFolder = 1, OR
 -- (2) Exit the proc with the return value (@PathExists) set to 0.
 SELECT @OS_Cmd = 'DIR "' + @FolderPath + '" '

 DELETE #OS_CMD_Results

 INSERT #OS_CMD_Results EXEC xp_cmdshell @OS_Cmd

 IF EXISTS (SELECT *
 FROM #OS_CMD_Results
 WHERE OS_Output LIKE '%File Not Found%'
 OR OS_Output LIKE '%The system cannot find the path specified.%'
 OR OS_Output LIKE '%The system cannot find the file specified.%')
 BEGIN
 IF @CreateFolder = 1
 BEGIN
 SELECT @OS_Cmd = 'MD "' + @FolderPath + '" '
 EXEC xp_cmdshell @OS_Cmd
 END
 ELSE
 BEGIN
 SELECT @PathExists = 0
 GOTO TheEnd
 END 
 END
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Retrieve the next values from the cursor
 FETCH NEXT FROM csrPath
 INTO @FolderPath,
 @PathComponent
 -- ******************************************************************************************

 END
 -- End of the loop
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Close and Deallocate the Cursor
 CLOSE csrPath
 DEALLOCATE csrPath 
 -- ******************************************************************************************

TheEnd:
 RETURN @PathExists

END
GO

-- (2) fn_DatabaseDetails

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_DeleteFiles] Script Date: 02/27/2007 14:28:04 ******/IF CHARINDEX('SQL Server 2005', @@Version) > 0
 BEGIN
 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_DatabaseDetails]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
 BEGIN
 PRINT 'Dropping function - SQL 2005'
 DROP FUNCTION [dbo].[fn_DatabaseDetails]
 END
 END
ELSE
 BEGIN
 IF EXISTS (SELECT * FROM sysobjects WHERE name = 'fn_DatabaseDetails' AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
 BEGIN
 PRINT 'Dropping function - SQL 2000'
 DROP FUNCTION [dbo].[fn_DatabaseDetails]
 END
 END

GO
IF CHARINDEX('SQL Server 2005', @@Version) > 0
 BEGIN
 EXEC dbo.sp_executesql @Statement = N'
 CREATE FUNCTION [dbo].[fn_DatabaseDetails] ()
 RETURNS @retDBDetails TABLE 
 (DBName VARCHAR(64),
 StateDesc VARCHAR(60),
 Recovery_Model_Desc VARCHAR(60),
 LastFullBackupDate DATETIME
 ) 
 -- --------------------------------------------------------------------------------------------------
 -- FUNCTION : [fn_DatabaseDetails]
 -- Description : Retrieve database details, used in procedure [sp_DatabaseBackup]
 -- SQL 2005.
 --
 -- Modification Log
 -- When Who Description
 -- 10/09/2007 Simon Facer Original Version.
 -- 12/14/2007 Simon Facer Added LastFullBackupDate to the returned table
 -- --------------------------------------------------------------------------------------------------

 AS

 BEGIN

 INSERT @retDBDetails
 SELECT d.[name],
 d.state_desc,
 d.recovery_model_desc,
 MAX(b.backup_finish_date) AS FullBackupCompleted
 FROM sys.databases d
 LEFT OUTER JOIN [msdb].[dbo].[backupset] b
 ON d.[name] = b.database_name
 AND b.[type] = ''D''
 WHERE d.[name] != ''tempdb''
 GROUP BY d.[name],
 d.state_desc,
 d.recovery_model_desc

 RETURN
 END'
 END

ELSE
 BEGIN
 EXEC dbo.sp_executesql @Statement = N'
 CREATE FUNCTION [dbo].[fn_DatabaseDetails] ()
 RETURNS @retDBDetails TABLE 
 (DBName VARCHAR(64),
 StateDesc VARCHAR(60),
 Recovery_Model_Desc VARCHAR(60),
 LastFullBackupDate DATETIME
 ) 
 -- --------------------------------------------------------------------------------------------------
 -- FUNCTION : [fn_DatabaseDetails]
 -- Description : Retrieve database details, used in procedure [sp_DatabaseBackup]
 -- SQL 2000.
 --
 -- Modification Log
 -- When Who Description
 -- 10/09/2007 Simon Facer Original Version.
 -- 12/14/2007 Simon Facer Added LastFullBackupDate to the returned table
 -- 04/04/2008 Simon Facer Modified logic to check DB Status - previous version was invalid.
 -- --------------------------------------------------------------------------------------------------

 AS

 BEGIN

 INSERT @retDBDetails
 SELECT d.[name], 
 CASE
 WHEN ((status & 32) +
 (status & 64) +
 (status & 128) +
 (status & 256) +
 (status & 512)) > 0
 THEN ''Offline''
 ELSE ''Online''
 END,
 CAST((DATABASEPROPERTYEX (d.[name], ''Recovery'') ) AS VARCHAR(60)),
 MAX(b.backup_finish_date) AS FullBackupCompleted
 FROM sysdatabases d
 LEFT OUTER JOIN [msdb].[dbo].[backupset] b
 ON d.[name] = b.database_name
 AND b.[type] = ''D''
 WHERE d.[name] != ''tempdb''
 GROUP BY d.[name],
 CASE
 WHEN ((status & 32) +
 (status & 64) +
 (status & 128) +
 (status & 256) +
 (status & 512)) > 0
 THEN ''Offline''
 ELSE ''Online''
 END,
 CAST((DATABASEPROPERTYEX (d.[name], ''Recovery'') ) AS VARCHAR(60))

 RETURN
 END'
 END
GO

IF CHARINDEX('SQL Server 2005', @@Version) > 0
 BEGIN
 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_DatabaseDetails]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
 BEGIN
 PRINT 'Function Created - SQL 2005'
 END
 END
ELSE
 BEGIN
 IF EXISTS (SELECT * FROM sysobjects WHERE name = 'fn_DatabaseDetails' AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
 BEGIN
 PRINT 'Function Created - SQL 2000'
 END
 END

GO

-- (3) sp_DatabaseBackup

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_DeleteFiles] Script Date: 02/27/2007 14:28:04 ******/IF CHARINDEX('SQL Server 2005', @@Version) > 0
 BEGIN
 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DatabaseBackup]') AND type in (N'P', N'PC'))
 BEGIN
 PRINT 'Dropping procedure - SQL 2005'
 DROP PROCEDURE [dbo].[sp_DatabaseBackup]
 END
 END
ELSE
 BEGIN
 IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_DatabaseBackup' AND type in (N'P', N'PC'))
 BEGIN
 PRINT 'Dropping procedure - SQL 2000'
 DROP PROCEDURE [dbo].[sp_DatabaseBackup]
 END
 END

GO
/****** Object: StoredProcedure [dbo].[sp_DatabaseBackup] Script Date: 10/08/2007 08:35:44 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_DatabaseBackup]
(
 @DBGroup VARCHAR(16) = NULL,
 @IncludeDBs VARCHAR(2048) = NULL,
 @ExcludeDBs VARCHAR(2048) = NULL,
 @BackupFolder VARCHAR(256),
 @FileSuffix VARCHAR(8) = NULL,
 @BackupType VARCHAR(8) = 'FULL',
 @CreateSubFolder BIT = 1,
 @VerifyBackup BIT = 1,
 @Debug BIT = 0
)

AS
-- --------------------------------------------------------------------------------------------------
-- Procedure : [sp_DatabaseBackup]
-- Description : To backup the specified databases.
-- Parameters DBGroup The group of dataabses to backup - System or User
-- OPTIONAL - defaults to NULL.
-- IncludeDBs Databases to be included, Ignored if DBGroup set.
-- MUST be comma-separated.
-- OPTIONAL - defaults to NULL.
-- NOTE - Either DBGroup or IncludeDBs is REQUIRED.
-- ExcludeDBs Databases to Exclude from DBGroup, ignored if IncludeDBs set.
-- MUST be comma-separated.
-- OPTIONAL - defaults to NULL.
-- BackupFolder Where to put the Backups.
-- REQUIRED.
-- FileSuffix Suffix to apply to the file, if this is missing, the suffix
-- will be set based on the backup type.
-- Defaulted values :
-- FULL backup : BAK
-- DIFF backup : DIF
-- TLOG backup : TRN
-- OPTIONAL - defaults to NULL.
-- BackupType What kind of backup to process.
-- Possible values (case-insensitive):
-- FULL
-- DIFF
-- TLOG
-- OPTIONAL - defaults to 'FULL'.
-- CreateSubFolder Should each backup be placed in its own folder under BackupFolder.
-- OPTIONAL - defaults to 1 (Yes).
-- VerifyBackup Should the Backups be verified.
-- OPTIONAL - defaults to 1 (Yes).
-- Debug Switch to determine if debugging information should be output
-- OPTIONAL - defaults to 0 (No).
--
-- Modification Log
-- When Who Description
-- 10/01/2007 Simon Facer Original Version
-- 10/19/2007 Simon Facer Added '[' and ']' brackets to database name in the BACKUP commands
-- 11/07/2007 Simon Facer Added Verify Backup option
-- 12/14/2007 Simon Facer Added logic to check for a previous Full Backup if a Differential
-- backup was requested, and default to a Full backup if no previous
-- Full backup exists.
-- --------------------------------------------------------------------------------------------------

BEGIN

SET NOCOUNT ON

 IF @Debug = 1
 BEGIN
 SELECT 'Parameters',
 @DBGroup AS DBGroup,
 @IncludeDBs AS IncludeDBs,
 @ExcludeDBs AS ExcludeDBs,
 @BackupFolder AS BackupFolder,
 @FileSuffix AS FileSuffix,
 @BackupType AS BackupType,
 @CreateSubFolder AS CreateSubFolder,
 @VerifyBackup AS VerifyBackup,
 @Debug AS Debug
 END

 DECLARE @SQL_Cmd VARCHAR(1024)
 DECLARE @OS_Cmd VARCHAR(1024)
 DECLARE @FileID INT
 DECLARE @FileIDMin INT
 DECLARE @FileIDMax INT
 DECLARE @Folder VARCHAR(128)
 DECLARE @TimeStamp VARCHAR(32)
 DECLARE @DBName VARCHAR(64)
 DECLARE @FullBackupDate DATETIME
 DECLARE @FileSuffix_Work VARCHAR(8)

 DECLARE @FullBackupBase VARCHAR(1024)
 DECLARE @DiffBackupBase VARCHAR(1024)
 DECLARE @TLogBackupBase VARCHAR(1024)
 DECLARE @VerifyBase VARCHAR(1024)


 -- ******************************************************************************************
 -- Create the # temp table to identify the databases to be backed up
 CREATE TABLE #Databases
 (DBName VARCHAR(64),
 StateDesc VARCHAR(60),
 Recovery_Model_Desc VARCHAR(60),
 LastFullBackupDate DATETIME
 )
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Validate the passed parameters
 -- (1) Required parameters
 IF ( LTRIM(RTRIM(@BackupFolder)) = '' )
 OR ( @DBGroup IS NULL
 AND (@IncludeDBs IS NULL OR LTRIM(RTRIM(@IncludeDBs)) = '')
 )
 BEGIN
 SELECT 'Backup Folder and DBGroup / IncludeDBs must be passed in'
 RAISERROR ('Backup Folder and DBGroup / IncludeDBs must be passed in', 16, 1)
 RETURN
 END

 -- (2) Valid DBGroup
 IF @DBGroup IS NOT NULL
 BEGIN
 IF @DBGroup != 'System' AND
 @DBGroup != 'User' AND
 @DBGroup != 'All'
 BEGIN
 SELECT 'DBGroup must be System, User or All'
 RAISERROR ('DBGroup must be either System, User or All', 16, 1)
 RETURN
 END
 END

 -- (3) Valid BackupType
 IF @DBGroup IS NOT NULL
 BEGIN
 IF @BackupType != 'Full' AND
 @BackupType != 'Diff' AND
 @BackupType != 'TLog'
 BEGIN
 SELECT 'BackupType must be one of Full / Diff / TLog'
 RAISERROR ('BackupType must be one of Full / Diff / TLog', 16, 1)
 RETURN
 END
 END
 -- ******************************************************************************************
 
 -- ******************************************************************************************
 -- If a FileSuffix wasnt passed in, default it based on the backup type
 IF @FileSuffix IS NULL OR
 LTRIM(RTRIM(@FileSuffix)) = ''
 BEGIN

 SELECT @FileSuffix = 
 CASE
 WHEN @BackupType = 'Full'
 THEN 'BAK'
 WHEN @BackupType = 'Diff'
 THEN 'DIF'
 WHEN @BackupType = 'TLog'
 THEN 'TRN'
 ELSE 'RAISERROR (''Invalid Backup Type Specified'', 16, 1)'
 END
 END
 -- ******************************************************************************************
 
 -- ******************************************************************************************
 -- Add a trailing '\' to the backup path if necessary
 IF RIGHT(@BackupFolder, 1) != '\'
 BEGIN
 SELECT @BackupFolder = @BackupFolder + '\'
 END
 -- ******************************************************************************************
 
 -- ******************************************************************************************
 -- Define the base SQL Command strings for each backup type
 SELECT @TimeStamp = SUBSTRING(REPLACE(REPLACE((REPLACE((CONVERT(VARCHAR(32), GETDATE(), 120)), '-', '')), ':', ''), ' ', ''), 1, 12)
 SELECT @FullBackupBase = 'BACKUP DATABASE [~d~] TO DISK=''' + @BackupFolder + '~sf~' + '~d~_' + @TimeStamp + '.~s~'' '
 SELECT @DiffBackupBase = 'BACKUP DATABASE [~d~] TO DISK=''' + @BackupFolder + '~sf~' + '~d~_' + @TimeStamp + '.~s~'' WITH DIFFERENTIAL'
 SELECT @TLogBackupBase = 'BACKUP LOG [~d~] TO DISK=''' + @BackupFolder + '~sf~' + '~d~_' + @TimeStamp + '.~s~'' '
 SELECT @VerifyBase = 'RESTORE VERIFYONLY FROM DISK=''' + @BackupFolder + '~sf~' + '~d~_' + @TimeStamp + '.~s~'' '
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Process any IncludeDBs data to add '[' and ']' values.
 SELECT @IncludeDBs = '[' + REPLACE(@IncludeDBs, ',', '],[') + ']'
 WHILE CHARINDEX('[ ', @IncludeDBs) > 0 
 BEGIN
 SELECT @IncludeDBs = REPLACE(@IncludeDBs, '[ ', '[')
 END
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Process any ExcludeDBs data to add '[' and ']' values.
 SELECT @ExcludeDBs = '[' + REPLACE(@ExcludeDBs, ',', '],[') + ']'
 WHILE CHARINDEX('[ ', @ExcludeDBs) > 0 
 BEGIN
 SELECT @ExcludeDBs = REPLACE(@ExcludeDBs, '[ ', '[')
 END
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Populate the #Databases table with all the databases on the server
 -- NOTE - [fn_DatabaseDetails] is specific to the SQL Version.
 INSERT #Databases
 SELECT *
 FROM [dbo].[fn_DatabaseDetails] ()
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- If a Group was specified, filter the database names
 IF @DBGroup IS NOT NULL
 BEGIN
 IF @DBGroup = 'System'
 BEGIN
 DELETE #Databases
 WHERE DBName NOT IN ('master', 'model', 'msdb')
 END

 ELSE 
 BEGIN
 IF @DBGroup = 'User'
 BEGIN
 DELETE #Databases
 WHERE DBName IN ('master', 'model', 'msdb')
 END
 END

 IF @ExcludeDBs IS NOT NULL AND
 LTRIM(RTRIM(@ExcludeDBs)) != ''
 BEGIN
 DELETE #Databases
 WHERE CHARINDEX(('[' + DBName + ']'), @ExcludeDBs) > 0
 END
 END
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- If a list of databases to include was specified and a DBGroup wasn't, process the 
 -- include list.
 IF @DBGroup IS NULL AND
 (LTRIM(RTRIM(@IncludeDBs)) != '')
 BEGIN
 DELETE #Databases
 WHERE CHARINDEX(('[' + DBName + ']'), @IncludeDBs) = 0
 END
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- If the backup type is TLOG, we dont want to try to backup databses in SIMPLE recovery mode
 IF @BackupType = 'TLog'
 BEGIN
 DELETE #Databases
 WHERE Recovery_Model_Desc = 'Simple'
 END
 -- ******************************************************************************************

 IF @Debug = 1
 BEGIN
 SELECT * 
 FROM #Databases
 END

 -- ******************************************************************************************
 -- Define the Cursor to loop throught the databases and back them up.
 DECLARE csrDatabases CURSOR FOR
 SELECT DBName,
 LastFullBackupDate
 FROM #Databases
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Open the Cursor, and retrieve the first value
 OPEN csrDatabases
 FETCH NEXT FROM csrDatabases
 INTO @DBName,
 @FullBackupDate
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- If Differential Backups have been requested, and no previous Full backup was completed,
 -- the Differential Backup will fail.
 -- If the date of the last full backup is missing, a Differential backup isswitched to a 
 -- Full backup instead.
 -- [fn_DatabaseDetails] pulls the last Full backup date from table [msdb].[dbo].[backupset],
 -- this is not infallible - if a database is deleted and then recreated with the same name,
 -- the data will still be in [msdb].[dbo].[backupset], showing (falsely) that there was a
 -- previous Full backup.
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Loop through the databases
 WHILE @@FETCH_STATUS = 0
 BEGIN

Retry_WithFullBackup:
 IF @Debug = 1
 BEGIN
 SELECT ('Processing ' + @DBName + ', Full Backup Date:' + CONVERT(VARCHAR(32), COALESCE(@FullBackupDate, '01/01/1900'), 109)) AS Progress
 END

 -- ******************************************************************************************
 -- Set the File Suffix ...
 IF @BackupType = 'Diff' AND
 @FullBackupDate IS NULL
 BEGIN
 SELECT @FileSuffix_Work = 'BAK'
 END
 ELSE
 BEGIN
 SELECT @FileSuffix_Work = @FileSuffix
 END
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Set the SQL Command appropriately
 SELECT @SQL_Cmd = 
 CASE 
 WHEN @BackupType = 'Diff' AND
 @FullBackupDate IS NULL
 THEN @FullBackupBase 
 WHEN @BackupType = 'Full'
 THEN @FullBackupBase
 WHEN @BackupType = 'Diff'
 THEN @DiffBackupBase
 WHEN @BackupType = 'TLog'
 THEN @TLogBackupBase
 ELSE 'RAISERROR (''Invalid Backup Type Specified'', 16, 1)'
 END
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Replace the substitution values for the current database
 SELECT @SQL_Cmd = REPLACE((REPLACE((REPLACE(@SQL_Cmd, '~d~', @DBName)), '~d~', @DBName)), '~s~', @FileSuffix_Work)
 
 IF @CreateSubFolder = 1
 BEGIN
 SELECT @SQL_Cmd = REPLACE(@SQL_Cmd, '~sf~', @DBName + '\')
 END
 ELSE
 BEGIN
 SELECT @SQL_Cmd = REPLACE(@SQL_Cmd, '~sf~', '')
 END
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Make sure the target folder exists
 SELECT @Folder = REVERSE(SUBSTRING(@SQL_Cmd, (CHARINDEX('DISK=', @SQL_Cmd) + 6), 999))
 SELECT @Folder = REVERSE(SUBSTRING(@Folder, (CHARINDEX('\', @Folder) + 1), 999))

 EXEC sp_PathExists @PathValue = @Folder
 -- ******************************************************************************************

 IF @Debug = 1
 BEGIN
 SELECT @SQL_Cmd AS BackupCommand
 END

 -- ******************************************************************************************
 -- Execute the backup command
 EXEC (@SQL_Cmd)

 IF @@ERROR != 0 AND
 @BackupType = 'Diff' AND
 @FullBackupDate IS NOT NULL
 BEGIN
 SELECT @FullBackupDate = NULL

 GOTO Retry_WithFullBackup
 END
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- If VerifyBackup was specified, execute the verify command
 IF @VerifyBackup = 1
 BEGIN

 SELECT @SQL_Cmd = @VerifyBase

 SELECT @SQL_Cmd = REPLACE((REPLACE((REPLACE(@SQL_Cmd, '~d~', @DBName)), '~d~', @DBName)), '~s~', @FileSuffix_Work)

 IF @CreateSubFolder = 1
 BEGIN
 SELECT @SQL_Cmd = REPLACE(@SQL_Cmd, '~sf~', @DBName + '\')
 END
 ELSE
 BEGIN
 SELECT @SQL_Cmd = REPLACE(@SQL_Cmd, '~sf~', '')
 END

 IF @Debug = 1
 BEGIN
 SELECT @SQL_Cmd AS RestoreVerifyCommand
 END

 EXEC (@SQL_Cmd)

 END
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Retrieve the next value from the Cursor
 FETCH NEXT FROM csrDatabases
 INTO @DBName,
 @FullBackupDate
 -- ******************************************************************************************

 END
 -- End of the Loop
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Close and Deallocate the Cursor
 CLOSE csrDatabases
 DEALLOCATE csrDatabases
 -- ******************************************************************************************

END


GO

IF CHARINDEX('SQL Server 2005', @@Version) > 0
 BEGIN
 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DatabaseBackup]') AND type in (N'P', N'PC'))
 BEGIN
 PRINT 'Procedure Created - SQL 2005'
 END
 END
ELSE
 BEGIN
 IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_DatabaseBackup' AND type in (N'P', N'PC'))
 BEGIN
 PRINT 'Procedure Created - SQL 2000'
 END
 END


-- (4) [sp_DeleteFiles]

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_DeleteFiles] Script Date: 02/27/2007 14:28:04 ******/IF CHARINDEX('SQL Server 2005', @@Version) > 0
 BEGIN
 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DeleteFiles]') AND type in (N'P', N'PC'))
 BEGIN
 PRINT 'Dropping procedure - SQL 2005'
 DROP PROCEDURE [dbo].[sp_DeleteFiles]
 END
 END
ELSE
 BEGIN
 IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_DeleteFiles' AND type in (N'P', N'PC'))
 BEGIN
 PRINT 'Dropping procedure - SQL 2000'
 DROP PROCEDURE [dbo].[sp_DeleteFiles]
 END
 END

GO
/****** Object: StoredProcedure [dbo].[sp_DeleteFiles] Script Date: 02/27/2007 14:28:15 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_DeleteFiles]
(
 @RootFolder VARCHAR(128) = NULL,
 @FileSuffix VARCHAR(8) = NULL,
 @ProcessSubFolders BIT = 0,
 @CutOffDate DATETIME = NULL,
 @CutOffDays INT = NULL,
 @ForceDeleteForReadonly BIT = 0,
 @Debug BIT = 0
)

-- --------------------------------------------------------------------------------------------------
-- Procedure : [sp_DeleteFiles]
-- Description : To scan all files in a folder and delete the files that are older than the
-- specified date.
-- Requires xp_cmdshell be enabled.
-- Parameters RootFolder The folder to search for files to delete.
-- REQUIRED.
-- FileSuffix Define the files to delete by file suffix,
-- ALL files are processed if NULL / '*' / BLANK.
-- OPTIONAL - defaults to NULL
-- ProcessSubFolder Switch to determine if the process should delete from
-- subfolders.
-- OPTIONAL - defaults to 0 (No).
-- CutOffDate Date which files must be older than for the process to
-- delete.
-- OPTIONAL - defaults to NULL.
-- CutOffDays Age (in days) that files must be older than for the
-- process to delete.
-- Value is always converted to 'Days Ago', whether +ve
-- or -ve value entered.
-- Value overrides CutOffDate, if both entered.
-- OPTIONAL - defaults to NULL.
-- NOTE - Either CutOffDate or CutOffDays is REQUIRED.
-- ForceDeleteForReadOnly Switch to determine if the process should delete even
-- if the file is READ-ONLY.
-- OPTIONAL - defaults to 0 (No).
-- Debug Switch to determine if debugging information should be output
-- OPTIONAL - defaults to 0 (No).
--
-- Modification Log
-- When Who Description
-- 02/27/2007 Simon Facer Original Version
-- 10/09/2007 Simon Facer Add logic to delete files without a suffix.
-- 10/16/2007 Simon Facer Add logic to handle File Dates without a trailing 'm', as in 
-- '10/14/2007 06:00p 34,172,416 ATC_Nevada_Data.DIFF'
-- --------------------------------------------------------------------------------------------------

AS

BEGIN

 DECLARE @OS_Cmd VARCHAR(1024)
 DECLARE @FileID INT
 DECLARE @FileIDMin INT
 DECLARE @FileIDMax INT
 DECLARE @Folder VARCHAR(128)

 CREATE TABLE #Files
 (
 FileID INT IDENTITY(1, 1) NOT NULL,
 Folder VARCHAR(128) NULL,
 FileName VARCHAR(128) NULL,
 FileExtension VARCHAR(8) NULL,
 FileDate DATETIME NULL,
 DirResult VARCHAR(512) NULL
 )

 SET NOCOUNT ON

 IF @Debug = 1
 BEGIN
 SELECT 'Parameters',
 @RootFolder AS RootFolder,
 @FileSuffix AS FileSuffix,
 @ProcessSubFolders AS ProcessSubFolders,
 @CutOffDate AS CutOffDate,
 @CutOffDays AS CutOffDays,
 @ForceDeleteForReadonly AS ForceDeleteForReadonly,
 @Debug as Debug
 END


 -- ******************************************************************************************
 -- Validate the passed parameters
 IF ( @RootFolder = '' )
 OR ( @CutOffDate IS NULL
 AND @CutOffDays IS NULL
 )
 BEGIN
 SELECT 'Root Folder and CutOffDays / CutOffDate must be passed in'
 RAISERROR ('Root Folder and CutOffDays / CutOffDate must be passed in', 16, 1)
 RETURN
 END
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Get the DIR results into the table
 SELECT @OS_Cmd = 'DIR "' + @RootFolder + '" ' + CASE @ProcessSubFolders
 WHEN 1 THEN ' /S'
 ELSE ' '
 END

 IF @Debug = 1
 BEGIN
 PRINT 'DIR Command ' + @OS_Cmd
 END

 INSERT #Files ( DirResult )
 EXECUTE master.dbo.xp_cmdshell @OS_Cmd
 -- ******************************************************************************************

 IF @Debug = 1
 BEGIN
 PRINT 'Completed DIR Command'
 SELECT 'DIR Output', *
 FROM #Files
 END

 -- ******************************************************************************************
 -- Locate the first 'Directory Of' entry
 SELECT @FileIDMin = MIN(FileID)
 FROM #Files
 WHERE CHARINDEX('DIRECTORY OF', UPPER(DirResult)) > 0
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Find the last File(s) entry
 SELECT @FileIDMax = MAX(FileID)
 FROM #Files
 WHERE CHARINDEX('FILE(S)', UPPER(DirResult)) > 0
 AND FileID < ( SELECT MAX(FileID)
 FROM #Files
 WHERE CHARINDEX('FILE(S)', UPPER(DirResult)) > 0
 )
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Delete the entries we dont need
 DELETE #Files
 WHERE FileID < @FileIDMin
 OR DirResult IS NULL
 OR CHARINDEX('<DIR>', UPPER(DirResult)) > 0
 OR CHARINDEX('FILE(S)', UPPER(DirResult)) > 0
 OR CHARINDEX('DIR(S)', UPPER(DirResult)) > 0
 OR FileID > @FileIDMax
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Find the Folders in the remaining output
 UPDATE #Files
 SET Folder = LTRIM(RTRIM(REPLACE(DirResult, 'Directory of ', ''))),
 DirResult = NULL
 WHERE CHARINDEX('DIRECTORY OF', UPPER(DirResult)) > 0
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Loop through the output to populate the Folders
 WHILE EXISTS ( SELECT *
 FROM #Files
 WHERE Folder IS NULL )
 BEGIN
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Find the first Folder entry
 SELECT @FileIDMin = MIN(FileID)
 FROM #Files
 WHERE Folder IS NULL
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Find the next folder entry, or a dummy entry past the end of the table
 SELECT @FileIDMax = COALESCE(MIN(FileID), 999999)
 FROM #Files
 WHERE Folder IS NOT NULL
 AND FileID > @FileIDMin
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Get the folder value for this loop
 SELECT @Folder = Folder
 FROM #Files
 WHERE FileID = ( SELECT MAX(FileID)
 FROM #Files
 WHERE FileID < @FileIDMin
 )
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Set the folder value for the files in this group
 UPDATE #Files
 SET Folder = @Folder
 WHERE FileID BETWEEN @FileIDMin AND @FileIDMax
 AND Folder IS NULL
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- End of the loop
 END
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Delete the Folder rows
 DELETE #Files
 WHERE DirResult IS NULL
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Get the OS File Date/Time
 UPDATE #Files
 SET FileDate = CAST((LTRIM(RTRIM(SUBSTRING(DirResult, 1, 22))) +
 CASE 
 WHEN UPPER(RIGHT((LTRIM(RTRIM(SUBSTRING(DirResult, 1, 22)))), 1)) = 'A'
 THEN 'M'
 WHEN UPPER(RIGHT((LTRIM(RTRIM(SUBSTRING(DirResult, 1, 22)))), 1)) = 'P'
 THEN 'M'
 ELSE ''
 END) AS DATETIME),
 DirResult = LTRIM(RTRIM(SUBSTRING(DirResult, 23, 99)))
 -- ******************************************************************************************


 -- ******************************************************************************************
 -- Get the filename from the DirResult field
 UPDATE #Files
 SET FileName = LTRIM(RTRIM(SUBSTRING(DirResult,
 ( CHARINDEX(' ', DirResult) ), 99)))
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Delete the entries without a file extension
 DELETE #Files
 WHERE CHARINDEX('.', Filename) = 0
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Get the file extension from the filename
 UPDATE #Files
 SET FileExtension = SUBSTRING(FileName,
 ( LEN(FileName) - ( CHARINDEX('.', ( REVERSE(FileName) )) - 2 ) ),
 ( CHARINDEX('.', ( REVERSE(FileName) )) - 1 ))
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- If a File Extension was passed in, delete the file names we aren't interested in
 IF @FileSuffix IS NOT NULL
 AND @FileSuffix != '*'
 AND LTRIM(RTRIM(@FileSuffix)) != ''
 BEGIN
 DELETE #Files
 WHERE FileExtension != @FileSuffix
 END
 -- ******************************************************************************************

 IF @Debug = 1
 BEGIN
 PRINT 'Final File List With Extensions'
 SELECT 'Final File List With Extensions', *
 FROM #Files
 END

 -- ******************************************************************************************
 -- Default the DirResult to NULL
 UPDATE #Files
 SET DirResult = NULL
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- If there is a Date/Time value in the file name, extract it
 -- Regular expression for '20YYMMDD HHNN', where YY Between 00 AND 19
 UPDATE #Files
 SET DirResult = '~1~' + SUBSTRING(FileName,
 PATINDEX('%20[01][0123456789][01][0123456789][0123][0123456789][ _][012][0123456789][012345][0123456789]%',
 FileName), 13)
 WHERE PATINDEX('%20[01][0123456789][01][0123456789][0123][0123456789][ _][012][0123456789][012345][0123456789]%',
 FileName) > 0
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- If there is a Date/Time value in the file name, extract it
 -- Regular expression for 'MMDD20YY HHNN', where YY Between 00 AND 19
 UPDATE #Files
 SET DirResult = '~2~' + SUBSTRING(FileName,
 PATINDEX('%[01][0123456789][0123][0123456789]20[01][0123456789][ _][012][0123456789][012345][0123456789]%',
 FileName), 13)
 WHERE PATINDEX('%[01][0123456789][0123][0123456789]20[01][0123456789][ _][012][0123456789][012345][0123456789]%',
 FileName) > 0
 AND DirResult IS NULL
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- If there is a Date value in the file name, extract it
 -- Regular expression for '20YYMMDD', where YY Between 00 AND 19
 UPDATE #Files
 SET DirResult = '~3~' + SUBSTRING(FileName,
 PATINDEX('%20[01][0123456789][01][0123456789][0123][0123456789]%',
 FileName), 8)
 WHERE PATINDEX('%20[01][0123456789][01][0123456789][0123][0123456789]%',
 FileName) > 0
 AND DirResult IS NULL
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- If there is a Date value in the file name, extract it
 -- Regular expression for 'MMDD20YY', where YY Between 00 AND 19
 UPDATE #Files
 SET DirResult = '~4~' + SUBSTRING(FileName,
 PATINDEX('%[01][0123456789][0123][0123456789]20[01][0123456789]%',
 FileName), 8)
 WHERE PATINDEX('%[01][0123456789][0123][0123456789]20[01][0123456789]%',
 FileName) > 0
 AND DirResult IS NULL
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Reformat the dates to include / and : characters (1)
 UPDATE #Files
 SET DirResult = SUBSTRING(DirResult, 4, 4) + '/' + SUBSTRING(DirResult, 8, 2)
 + '/' + SUBSTRING(DirResult, 10, 2) + ' ' + SUBSTRING(DirResult, 13, 2)
 + ':' + SUBSTRING(DirResult, 15, 2)
 WHERE SUBSTRING(DirResult, 1, 3) = '~1~'
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Reformat the dates to include / and : characters (2)
 UPDATE #Files
 SET DirResult = SUBSTRING(DirResult, 4, 2) + '/' + SUBSTRING(DirResult, 6, 2)
 + '/' + SUBSTRING(DirResult, 8, 4) + ' ' + SUBSTRING(DirResult, 13, 2)
 + ':' + SUBSTRING(DirResult, 15, 2)
 WHERE SUBSTRING(DirResult, 1, 3) = '~2~'
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Reformat the dates to include / and : characters (3)
 UPDATE #Files
 SET DirResult = SUBSTRING(DirResult, 4, 4) + '/' + SUBSTRING(DirResult, 8, 2)
 + '/' + SUBSTRING(DirResult, 10, 2)
 WHERE SUBSTRING(DirResult, 1, 3) = '~3~'
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Reformat the dates to include / and : characters (4)
 UPDATE #Files
 SET DirResult = SUBSTRING(DirResult, 4, 2) + '/' + SUBSTRING(DirResult, 6, 2)
 + '/' + SUBSTRING(DirResult, 8, 4)
 WHERE SUBSTRING(DirResult, 1, 3) = '~4~'
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- The File Date overrides the OS File date
 UPDATE #Files
 SET FileDate = CAST(DirResult AS DATETIME)
 WHERE ISDATE(DirResult) = 1
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Calculate the CutOffDate, if CutOffDays was passed in
 IF @CutOffDays IS NOT NULL
 AND @CutOffDays != 0
 BEGIN
 IF @CutOffDays > 0
 BEGIN
 SELECT @CutOffDays = -1 * @CutOffDays
 END
 SELECT @CutOffDate = DATEADD(DAY, @CutOffDays, GETDATE())
 END
 -- ******************************************************************************************

 IF @Debug = 1
 BEGIN
 PRINT 'CutOff Date' + CONVERT(VARCHAR(32), @CutOffDate)
 SELECT 'CutOff Date', @CutOffDate AS CutOffDate
 END

 -- ******************************************************************************************
 -- Get rid of the rows where the FileDate isn't outside the cutoff
 IF @Debug = 1
 BEGIN
 PRINT 'File List before CutOff date applied'
 SELECT 'File List before CutOff date applied', *
 FROM #Files
 END

 DELETE #Files
 WHERE FileDate !< @CutOffDate

 IF @Debug = 1
 BEGIN
 PRINT 'File List with CutOff date applied'
 SELECT 'File List with CutOff date applied', *
 FROM #Files
 END
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Loop through the files to be deleted
 WHILE EXISTS ( SELECT *
 FROM #Files )
 BEGIN
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Generate the DELETE command for the current file
 SET ROWCOUNT 1
 SELECT @OS_Cmd = 'DEL /Q' +
 CASE @ForceDeleteForReadonly
 WHEN 0 THEN ' '
 ELSE ' /F '
 END +
 '"' + Folder + '\' + FileName + '" ',
 @FileID = FileID
 FROM #Files
 SET ROWCOUNT 0
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Delete the file
 IF @Debug = 1
 BEGIN
 PRINT 'Delete Command ' + @OS_Cmd
 SELECT 'Delete Command', @OS_Cmd
 END

 EXECUTE master.dbo.xp_cmdshell @OS_Cmd
 --PRINT @OS_Cmd

 IF @Debug = 1
 BEGIN
 PRINT 'Delete Completed'
 END
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- Delete the row just processed
 DELETE #Files
 WHERE FileID = @FileID
 -- ******************************************************************************************

 -- ******************************************************************************************
 -- End of WHILE loop
 END
 -- ******************************************************************************************

END

GO

IF CHARINDEX('SQL Server 2005', @@Version) > 0
 BEGIN
 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DeleteFiles]') AND type in (N'P', N'PC'))
 BEGIN
 PRINT 'Procedure Created - SQL 2005'
 END
 END
ELSE
 BEGIN
 IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_DeleteFiles' AND type in (N'P', N'PC'))
 BEGIN
 PRINT 'Procedure Created - SQL 2000'
 END
 END

GO

IF NOT EXISTS (SELECT *
 FROM #Folders
 WHERE LTRIM(RTRIM(COALESCE(BackupFolder, ''))) != '' AND
 LTRIM(RTRIM(COALESCE(LogFolder, ''))) != '')
 BEGIN
 RAISERROR ('Jobs Not Created - Backup and Log folders must be specified', 16, 1)
 END

GO


-- (5) Example Jobs
--     File Cleanup - Deletes Full backups (.BAK) after 7 days
-- Deleted Differential backups (.DIF) after 2 days
-- Deleted T-Log backups (.TRN) after 2 days
-- Deleted log files (.TXT) after 7 days
-- System DB - Full Backups Runs weekly, Fridays at 6:00pm
-- User DB - Differential Backups Runs daily, except Friday, at 6:00pm
-- User DB - Full Backups Runs weekly, Fridays at 6:00pm
-- User DB - Transaction Log Backups Runs daily, at 12:00am


USE [msdb]
GO
DECLARE @BackupFolder NVARCHAR(256)
DECLARE @LogFolder NVARCHAR(256)
DECLARE @StepCommand NVARCHAR(2048)
DECLARE @StepLog NVARCHAR(1024)
SELECT @BackupFolder = BackupFolder,
 @LogFolder = LogFolder
 FROM #Folders

/****** Object: Job [File Cleanup] Script Date: 10/18/2007 07:47:31 ******/IF NOT EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'File Cleanup') AND 
 EXISTS (SELECT *
 FROM #Folders
 WHERE LTRIM(RTRIM(COALESCE(BackupFolder, ''))) != '' AND
 LTRIM(RTRIM(COALESCE(LogFolder, ''))) != '')
BEGIN

 BEGIN TRANSACTION
 DECLARE @ReturnCode INT
 SELECT @ReturnCode = 0
 /****** Object: JobCategory [Database Maintenance] Script Date: 10/18/2007 07:47:31 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
 BEGIN
 EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 END

 DECLARE @jobId BINARY(16)
 EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'File Cleanup', 
         @enabled=0, 
         @notify_level_eventlog=0, 
         @notify_level_email=0, 
         @notify_level_netsend=0, 
         @notify_level_page=0, 
         @delete_level=0, 
         @description=N'Delete old Backup and Report files', 
         @category_name=N'Database Maintenance', 
         @owner_login_name=N'sa', @job_id = @jobId OUTPUT
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 /****** Object: Step [Delete Full Backup files] Script Date: 10/18/2007 07:47:32 ******/ SELECT @StepCommand= 'EXECUTE [master].[dbo].[sp_DeleteFiles] 
 @RootFolder = ''' + @BackupFolder + '''
 ,@FileSuffix = ''BAK''
 ,@ProcessSubFolders = 1
 ,@CutOffDate = NULL
 ,@CutOffDays = 7
 ,@ForceDeleteForReadonly = 1
 ,@Debug = 0' 
 EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete Full Backup files', 
         @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=@StepCommand, 
         @database_name=N'master', 
         @flags=0
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 /****** Object: Step [Delete Differential Backup Files] Script Date: 10/18/2007 07:47:32 ******/ SELECT @StepCommand= 'EXECUTE [master].[dbo].[sp_DeleteFiles] 
 @RootFolder = ''' + @BackupFolder + '''
 ,@FileSuffix = ''DIF''
 ,@ProcessSubFolders = 1
 ,@CutOffDate = NULL
 ,@CutOffDays = 2
 ,@ForceDeleteForReadonly = 1
 ,@Debug = 0' 
 EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete Differential Backup Files', 
         @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=@StepCommand, 
         @database_name=N'master', 
         @flags=0
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 /****** Object: Step [Delete Transaction Log Backup Files] Script Date: 10/18/2007 07:47:32 ******/ SELECT @StepCommand= 'EXECUTE [master].[dbo].[sp_DeleteFiles] 
 @RootFolder = ''' + @BackupFolder + '''
 ,@FileSuffix = ''TRN''
 ,@ProcessSubFolders = 1
 ,@CutOffDate = NULL
 ,@CutOffDays = 2
 ,@ForceDeleteForReadonly = 1
 ,@Debug = 0' 
 EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete Transaction Log Backup Files', 
         @step_id=3, 
         @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=@StepCommand, 
         @database_name=N'master', 
         @flags=0
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 /****** Object: Step [Delete Log Report Files] Script Date: 10/18/2007 07:47:32 ******/ SELECT @StepCommand= 'EXECUTE [master].[dbo].[sp_DeleteFiles] 
 @RootFolder = ''' + @LogFolder + '''
 ,@FileSuffix = ''TXT''
 ,@ProcessSubFolders = 1
 ,@CutOffDate = NULL
 ,@CutOffDays = 7
 ,@ForceDeleteForReadonly = 1
 ,@Debug = 0' 
 EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete Log Report Files', 
         @step_id=4, 
         @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=@StepCommand, 
         @database_name=N'master', 
         @flags=0
 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'Delete Old Files', 
         @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=20071009, 
         @active_end_date=99991231, 
         @active_start_time=170000, 
         @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:

END
GO

USE [msdb]
GO
DECLARE @BackupFolder VARCHAR(256)
DECLARE @LogFolder VARCHAR(256)
DECLARE @StepCommand NVARCHAR(2048)
DECLARE @StepLog NVARCHAR(1024)
SELECT @BackupFolder = BackupFolder,
 @LogFolder = LogFolder
 FROM #Folders

/****** Object: Job [System DB - Full Backups] Script Date: 10/18/2007 07:57:17 ******/IF NOT EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'System DB - Full Backups') AND 
 EXISTS (SELECT *
 FROM #Folders
 WHERE LTRIM(RTRIM(COALESCE(BackupFolder, ''))) != '' AND
 LTRIM(RTRIM(COALESCE(LogFolder, ''))) != '')
BEGIN

 BEGIN TRANSACTION
 DECLARE @ReturnCode INT
 SELECT @ReturnCode = 0
 /****** Object: JobCategory [Database Maintenance] Script Date: 10/18/2007 07:57:17 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
 BEGIN
 EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 END

 DECLARE @jobId BINARY(16)
 EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'System DB - Full Backups', 
         @enabled=0, 
         @notify_level_eventlog=0, 
         @notify_level_email=0, 
         @notify_level_netsend=0, 
         @notify_level_page=0, 
         @delete_level=0, 
         @description=N'No description available.', 
         @category_name=N'Database Maintenance', 
         @owner_login_name=N'sa', @job_id = @jobId OUTPUT
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 /****** Object: Step [Full Backup] Script Date: 10/18/2007 07:57:17 ******/ SELECT @StepCommand= '[sp_DatabaseBackup]
 @DBGroup = ''System'', -- OPTIONAL. User / System / All / [NULL]. Over-rides @IncludeDBs
 @IncludeDBs = NULL, -- OPTIONAL. Comma-separated list of database to include. @DBGroup *or* @IncludeDBs must be entered.
 @ExcludeDBs = NULL, -- OPTIONAL. Comma-separated list of database to exclude, operates in conjunction with @DBGroup.
 @BackupFolder = ''' + @BackupFolder + ''', -- REQUIRED. Target Folder, should NOT include DB specific folder - use @CreateSubFolder.
 @FileSuffix = NULL, -- OPTIONAL. BAK / DIF / TRN / [NULL], will default based on @BackupType if omitted.
 @BackupType = ''FULL'', -- OPTIONAL. FULL / DIFF / TLOG, will default to FULL if omitted.
 @CreateSubFolder = 1, -- OPTIONAL. Create sub-folders for each database, defaults to 1 (Yes).
 @VerifyBackup = 1, -- OPTIONAL. Should the Backups be verified.
 @Debug = 0 -- OPTIONAL. Display Debug information, defaults to 0 (No).',
 @StepLog = @LogFolder + '\System DB.Full Backups.txt'
 EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Full Backup', 
         @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=@StepCommand, 
         @database_name=N'master', 
         @output_file_name=@StepLog, 
         @flags=0
 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'System DB - Full Backup', 
         @enabled=1, 
         @freq_type=8, 
         @freq_interval=32, 
         @freq_subday_type=1, 
         @freq_subday_interval=0, 
         @freq_relative_interval=0, 
         @freq_recurrence_factor=1, 
         @active_start_date=20071009, 
         @active_end_date=99991231, 
         @active_start_time=180000, 
         @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:

END
GO

USE [msdb]
GO
DECLARE @BackupFolder VARCHAR(256)
DECLARE @LogFolder VARCHAR(256)
DECLARE @StepCommand NVARCHAR(2048)
DECLARE @StepLog NVARCHAR(1024)
SELECT @BackupFolder = BackupFolder,
 @LogFolder = LogFolder
 FROM #Folders

/****** Object: Job [User DB - Differential Backups] Script Date: 10/18/2007 07:52:34 ******/IF NOT EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'User DB - Differential Backups') AND 
 EXISTS (SELECT *
 FROM #Folders
 WHERE LTRIM(RTRIM(COALESCE(BackupFolder, ''))) != '' AND
 LTRIM(RTRIM(COALESCE(LogFolder, ''))) != '')
BEGIN

 BEGIN TRANSACTION
 DECLARE @ReturnCode INT
 SELECT @ReturnCode = 0
 /****** Object: JobCategory [Database Maintenance] Script Date: 10/18/2007 07:52:34 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
 BEGIN
 EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 END

 DECLARE @jobId BINARY(16)
 EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'User DB - Differential Backups', 
         @enabled=0, 
         @notify_level_eventlog=0, 
         @notify_level_email=0, 
         @notify_level_netsend=0, 
         @notify_level_page=0, 
         @delete_level=0, 
         @description=N'No description available.', 
         @category_name=N'Database Maintenance', 
         @owner_login_name=N'sa', @job_id = @jobId OUTPUT
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 /****** Object: Step [Differential Backup] Script Date: 10/18/2007 07:52:34 ******/ SELECT @StepCommand= '[sp_DatabaseBackup]
 @DBGroup = ''User'', -- OPTIONAL. User / System / All / [NULL]. Over-rides @IncludeDBs
 @IncludeDBs = NULL, -- OPTIONAL. Comma-separated list of database to include. @DBGroup *or* @IncludeDBs must be entered.
 @ExcludeDBs = NULL, -- OPTIONAL. Comma-separated list of database to exclude, operates in conjunction with @DBGroup.
 @BackupFolder = ''' + @BackupFolder + ''', -- REQUIRED. Target Folder, should NOT include DB specific folder - use @CreateSubFolder.
 @FileSuffix = NULL, -- OPTIONAL. BAK / DIF / TRN / [NULL], will default based on @BackupType if omitted.
 @BackupType = ''DIFF'', -- OPTIONAL. FULL / DIFF / TLOG, will default to FULL if omitted.
 @CreateSubFolder = 1, -- OPTIONAL. Create sub-folders for each database, defaults to 1 (Yes).
 @VerifyBackup = 1, -- OPTIONAL. Should the Backups be verified.
 @Debug = 0 -- OPTIONAL. Display Debug information, defaults to 0 (No).',
 @StepLog = @LogFolder + '\User DB.Differential Backups.txt'
 EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Differential Backup', 
         @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=@StepCommand, 
         @database_name=N'master', 
         @output_file_name=@StepLog, 
         @flags=0
 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'User DB - Differential Backup', 
         @enabled=1, 
         @freq_type=8, 
         @freq_interval=95, 
         @freq_subday_type=1, 
         @freq_subday_interval=0, 
         @freq_relative_interval=0, 
         @freq_recurrence_factor=1, 
         @active_start_date=20071009, 
         @active_end_date=99991231, 
         @active_start_time=180000, 
         @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:

END
GO

USE [msdb]
GO
DECLARE @BackupFolder VARCHAR(256)
DECLARE @LogFolder VARCHAR(256)
DECLARE @StepCommand NVARCHAR(2048)
DECLARE @StepLog NVARCHAR(1024)
SELECT @BackupFolder = BackupFolder,
 @LogFolder = LogFolder
 FROM #Folders

/****** Object: Job [User DB - Full Backups] Script Date: 10/18/2007 07:54:50 ******/IF NOT EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'User DB - Full Backups') AND 
 EXISTS (SELECT *
 FROM #Folders
 WHERE LTRIM(RTRIM(COALESCE(BackupFolder, ''))) != '' AND
 LTRIM(RTRIM(COALESCE(LogFolder, ''))) != '')
BEGIN

 BEGIN TRANSACTION
 DECLARE @ReturnCode INT
 SELECT @ReturnCode = 0
 /****** Object: JobCategory [Database Maintenance] Script Date: 10/18/2007 07:54:50 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
 BEGIN
 EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 END

 DECLARE @jobId BINARY(16)
 EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'User DB - Full Backups', 
         @enabled=0, 
         @notify_level_eventlog=0, 
         @notify_level_email=0, 
         @notify_level_netsend=0, 
         @notify_level_page=0, 
         @delete_level=0, 
         @description=N'No description available.', 
         @category_name=N'Database Maintenance', 
         @owner_login_name=N'sa', @job_id = @jobId OUTPUT
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 /****** Object: Step [Full Backup] Script Date: 10/18/2007 07:54:51 ******/ SELECT @StepCommand= '[sp_DatabaseBackup]
 @DBGroup = ''User'', -- OPTIONAL. User / System / All / [NULL]. Over-rides @IncludeDBs
 @IncludeDBs = NULL, -- OPTIONAL. Comma-separated list of database to include. @DBGroup *or* @IncludeDBs must be entered.
 @ExcludeDBs = NULL, -- OPTIONAL. Comma-separated list of database to exclude, operates in conjunction with @DBGroup.
 @BackupFolder = ''' + @BackupFolder + ''', -- REQUIRED. Target Folder, should NOT include DB specific folder - use @CreateSubFolder.
 @FileSuffix = NULL, -- OPTIONAL. BAK / DIF / TRN / [NULL], will default based on @BackupType if omitted.
 @BackupType = ''FULL'', -- OPTIONAL. FULL / DIFF / TLOG, will default to FULL if omitted.
 @CreateSubFolder = 1, -- OPTIONAL. Create sub-folders for each database, defaults to 1 (Yes).
 @VerifyBackup = 1, -- OPTIONAL. Should the Backups be verified.
 @Debug = 0 -- OPTIONAL. Display Debug information, defaults to 0 (No).',
 @StepLog = @LogFolder + '\User DB.Full Backups.txt'
 EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Full Backup', 
         @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=@StepCommand, 
         @database_name=N'master', 
         @output_file_name=@StepLog, 
         @flags=0
 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'User DB - Full Backup', 
         @enabled=1, 
         @freq_type=8, 
         @freq_interval=32, 
         @freq_subday_type=1, 
         @freq_subday_interval=0, 
         @freq_relative_interval=0, 
         @freq_recurrence_factor=1, 
         @active_start_date=20071009, 
         @active_end_date=99991231, 
         @active_start_time=180000, 
         @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:

END
GO

USE [msdb]
GO
DECLARE @BackupFolder VARCHAR(256)
DECLARE @LogFolder VARCHAR(256)
DECLARE @StepCommand NVARCHAR(2048)
DECLARE @StepLog NVARCHAR(1024)
SELECT @BackupFolder = BackupFolder,
 @LogFolder = LogFolder
 FROM #Folders

/****** Object: Job [User DB - Transaction Log Backups] Script Date: 10/18/2007 07:55:45 ******/IF NOT EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'User DB - Transaction Log Backups') AND 
 EXISTS (SELECT *
 FROM #Folders
 WHERE LTRIM(RTRIM(COALESCE(BackupFolder, ''))) != '' AND
 LTRIM(RTRIM(COALESCE(LogFolder, ''))) != '')
BEGIN

 BEGIN TRANSACTION
 DECLARE @ReturnCode INT
 SELECT @ReturnCode = 0
 /****** Object: JobCategory [Database Maintenance] Script Date: 10/18/2007 07:55:45 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
 BEGIN
 EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 END

 DECLARE @jobId BINARY(16)
 EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'User DB - Transaction Log Backups', 
         @enabled=0, 
         @notify_level_eventlog=0, 
         @notify_level_email=0, 
         @notify_level_netsend=0, 
         @notify_level_page=0, 
         @delete_level=0, 
         @description=N'No description available.', 
         @category_name=N'Database Maintenance', 
         @owner_login_name=N'sa', @job_id = @jobId OUTPUT
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 /****** Object: Step [Transaction Log Backup] Script Date: 10/18/2007 07:55:45 ******/ SELECT @StepCommand= '[sp_DatabaseBackup]
 @DBGroup = ''User'', -- OPTIONAL. User / System / All / [NULL]. Over-rides @IncludeDBs
 @IncludeDBs = NULL, -- OPTIONAL. Comma-separated list of database to include. @DBGroup *or* @IncludeDBs must be entered.
 @ExcludeDBs = NULL, -- OPTIONAL. Comma-separated list of database to exclude, operates in conjunction with @DBGroup.
 @BackupFolder = ''' + @BackupFolder + ''', -- REQUIRED. Target Folder, should NOT include DB specific folder - use @CreateSubFolder.
 @FileSuffix = NULL, -- OPTIONAL. BAK / DIF / TRN / [NULL], will default based on @BackupType if omitted.
 @BackupType = ''TLOG'', -- OPTIONAL. FULL / DIFF / TLOG, will default to FULL if omitted.
 @CreateSubFolder = 1, -- OPTIONAL. Create sub-folders for each database, defaults to 1 (Yes).
 @VerifyBackup = 1, -- OPTIONAL. Should the Backups be verified.
 @Debug = 0 -- OPTIONAL. Display Debug information, defaults to 0 (No).',
 @StepLog = @LogFolder + '\User DB.TLog Backups.txt'
 EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Transaction Log Backup', 
         @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=@StepCommand, 
         @database_name=N'master', 
         @output_file_name=@StepLog, 
         @flags=0
 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'User DB - Transaction Log Backup', 
         @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=20071009, 
         @active_end_date=99991231, 
         @active_start_time=0, 
         @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:

END

GO

DROP TABLE #Folders

GO

Rate

4.4 (5)

Share

Share

Rate

4.4 (5)