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 
