CREATE PROC [dbo].[BackupDatabases](
-- Databases to backup in a comma-separated list.
-- e.g. database1,database2,database3
-- NULL = Backup all databases
@Databases NVARCHAR(MAX)=NULL,
-- Databases to be excluded from the backup in a comma-separated list
-- e.g. database1,database2,database3
-- NULL = Don't exclude any databases
@ExcludedDatabases NVARCHAR(MAX)=NULL,
-- Type of Backup to perform
-- FULL = full database backup, DIFF = differential database Backup, TRAN = transaaction log backup
@BackupType CHAR(4)='FULL',
-- Backup Directory
-- e.g. \\BackupServer\BackupShare or C:\Backups
-- Multiple locations can be specified using a pipe symbol.
-- e.g. C:\Backups|D:\Backups|E:\Backups
-- Note: If you just want to split the backup into multiple parts, you can also specify the same location multiple times. e.g.
-- C:\Backups|C:\Backups|C:\Backups
-- When multiple locations are specifed, the file name is appended with the file number and number of files. e.g. 1of3, 2of3, 3of3
@BackupDir NVARCHAR(MAX),
-- Run VERIFYONLY check after backup has completed
-- 1 = Verify, 0 = Don't verify
@Verify BIT=0,
-- Perform backup with CHECKSUM option
-- 1 = Perform CHECKSUM, 0 = Don't Perform CHECKSUM
@CheckSum BIT=0,
-- Option to perform DBCC CHECKDB command before backup
-- 0 = don't perform DBCC check, 1 = perform DBCC check, 2 = perform DBCC check with physical_only option
@PerformDBCC TINYINT=0,
-- Option to remove backup files after a specified number of hours
-- e.g. 24 = Keep backups for 1 day, 168 = Keep backups for 7 days (24*7)
-- NULL = Don't remove backup files
@RetainHours INT=NULL,
-- Option to delete old backup files before performing backup.
-- Ideally you want to ensure that you have a valid backup before deleting old backup files so this option is best set to zero.
-- 0 = remove after backup completed, 1 = remove before backup completed, NULL = Backup files not removed
@DeleteBeforeBackup BIT=0,
-- Option to debug this stored procedure
-- 1 = Debug Mode (Print Commands), 0 = Execute Mode (Perform Backups)
@Debug BIT=0
)
AS
/*
Created By: David Wiseman
Date: 2009-12-01
Website: http://www.wisesoft.co.uk
Description:
SQL Server database backup script. Can be used to backup all databases on a SQL server instance or include/exclude specific databases.
Backups can be automatically deleted after a specified period of time.
Examples:
EXEC dbo.BackupDatabases @BackupDir='C:\Backups'
EXEC dbo.BackupDatabases @BackupDir='C:\Backups',@RetainHours=336 -- Delete after 2 weeks
EXEC dbo.BackupDatabases @BackupDir='C:\Backups',@RetainHours=336, -- Delete after 2 weeks
@ExcludedDatabases='nobackupdb1,nobackupdb2',
@BackupType='DIFF'
Requires SQL CLR IO Utility and dbo.SplitString function:
http://www.wisesoft.co.uk/articles/sql_server_clr_io_utility.aspx
http://www.wisesoft.co.uk/scripts/t-sql_cte_split_string_function.aspx
*/
SET NOCOUNT ON;
DECLARE @Database sysname;
DECLARE @FileNamePattern nvarchar(1024);
DECLARE @BackupCommand nvarchar(max);
DECLARE @BackupName nvarchar(max);
DECLARE @BackupDBs TABLE(name sysname);
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorCount INT;
DECLARE @BackupLocations NVARCHAR(MAX)
SET @ErrorCount = 0
-- Check that a valid backup type is specified
IF @BackupType NOT IN('FULL','DIFF','TRAN')
BEGIN;
RAISERROR ('Invalid Backup Type Specified. Options: FULL,DIFF,TRAN',11,1);
RETURN;
END;
IF @Databases IS NULL
BEGIN -- Backup of all databases required (excluding databases that are not applicable for backup type)
INSERT INTO @BackupDBs(name)
SELECT name
FROM sys.databases db
WHERE source_database_id IS NULL -- Exclude database snapshots
AND name <> 'tempdb' -- Exclude tempdb database
AND [state] = 0 --ONLINE databases only
AND is_in_standby=0 -- Exclude databases in standby mode
AND NOT (@BackupType='DIFF' AND name='master') -- Exclude master DB for Diff backups
AND NOT (@BackupType='TRAN' AND recovery_model_desc='SIMPLE'); -- Exclude "SIMPLE" databases from TRAN backups
END
ELSE
BEGIN;
-- Parse CSV string of database into temp table
INSERT INTO @BackupDBs(name)
SELECT value
FROM dbo.fnSplitString(@Databases,',');
END;
IF @ExcludedDatabases IS NOT NULL
BEGIN;
-- Remove any excluded databases from backup list
DELETE B
FROM @BackupDBs B
WHERE EXISTS(SELECT *
FROM dbo.fnSplitString(@ExcludedDatabases,',') excl
WHERE excl.value = B.Name);
END;
DECLARE cBackup CURSOR FAST_FORWARD FOR
SELECT name
FROM @BackupDBs;
OPEN cBackup;
FETCH NEXT FROM cBackup INTO @Database;
WHILE @@FETCH_STATUS = 0
BEGIN;
-- Check that we can perform the selected backup type on the database
IF NOT EXISTS(SELECT name
FROM sys.databases db
WHERE name = @Database
AND source_database_id IS NULL -- Exclude database snapshots
AND name <> 'tempdb' -- Exclude tempdb database
AND [state] = 0 --ONLINE databases only
AND is_in_standby=0 -- Exclude databases in standby mode
AND NOT (@BackupType='DIFF' AND name='master') -- Exclude master DB for Diff backups
AND NOT (@BackupType='TRAN' AND recovery_model_desc='SIMPLE') -- Exclude "SIMPLE" databases from TRAN backups
)
BEGIN;
-- Backup type isn't valid for the database
-- e.g. Database doesn't exist, TRAN backup specified for a SIMPLE database etc.
SELECT @ErrorMessage = 'BackupDatabases Error: WARNING: ' + @BackupType + ' backup is invalid for database "' + @Database + '". Backup Skipped.',
@ErrorSeverity=10,@ErrorState=1,
@ErrorCount = @ErrorCount + 1;
-- Log error (Severity of 10 won't cause agent job to fail)
RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH LOG;
-- Get next database and continue processing
FETCH NEXT FROM cBackup INTO @Database;
CONTINUE;
END;
IF @PerformDBCC = 1
BEGIN; -- Perform a DBCC CHECKDB command before backup
IF @Debug = 1
BEGIN;
PRINT 'DBCC CHECKDB (' + QUOTENAME(@Database,'''') + ') WITH NO_INFOMSGS';
END;
ELSE
BEGIN;
DBCC CHECKDB (@Database) WITH NO_INFOMSGS;
END;
END;
ELSE IF @PerformDBCC = 2
BEGIN -- Perform a DBCC CHECKDB (PHYSICAL_ONLY option) before backup
IF @Debug = 1
BEGIN;
PRINT 'DBCC CHECKDB (' + QUOTENAME(@Database,'''') + ') WITH NO_INFOMSGS, PHYSICAL_ONLY';
END;
ELSE
BEGIN;
DBCC CHECKDB (@Database) WITH NO_INFOMSGS, PHYSICAL_ONLY;
END;
END
-- FileName pattern (Excluding the date)
-- e.g. SERVER.DATABASENAME.FULL.
SET @FileNamePattern = REPLACE(@@SERVERNAME,'\','_') + '.' + REPLACE(@Database,'.','_')
+ '.' + UPPER(@BackupType) + '.';
-- Get label for backup
SET @BackupName = @Database + '-' + @BackupType + ' Database Backup';
SELECT @BackupLocations = STUFF((
SELECT ',DISK=N' + QUOTENAME(value + '\' + @FileNamePattern + REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(),120),'-',''),':',''),' ','') +
-- If multiple locations are specified,
ISNULL('_[' + CAST(ROW_NUMBER() OVER(ORDER BY value) as NVARCHAR(MAX)) + 'of' + CAST(NULLIF(COUNT(*) OVER(),1) as NVARCHAR(MAX)) + ']','')
+ '.BAK','''') + '
'
FROM dbo.fnSplitString(@BackupDir,'|')
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')
,1,1,'')
IF @BackupType = 'FULL'
BEGIN;
SET @BackupCommand = N'BACKUP DATABASE ' + QUOTENAME(@Database) + N'
TO ' + @BackupLocations +
'WITH Name=N' + QUOTENAME(@BackupName,'''');
END;
ELSE IF @BackupType = 'DIFF'
BEGIN;
SET @BackupCommand = N'BACKUP DATABASE ' + QUOTENAME(@Database) + N'
TO ' + @BackupLocations +
'WITH DIFFERENTIAL,Name=N' + QUOTENAME(@BackupName,'''');
END;
ELSE IF @BackupType = 'TRAN'
BEGIN;
SET @BackupCommand = N'BACKUP LOG ' + QUOTENAME(@Database) + N'
TO ' + @BackupLocations +
'WITH Name=N' + QUOTENAME(@BackupName,'''');
END;
IF @CheckSum = 1
BEGIN;
SET @BackupCommand = @BackupCommand + ',CHECKSUM';
END;
-- Removes old backups for this database if the @DeleteBeforeBackup is specified (Otherwise cleanup occurs after the backup)
IF @DeleteBeforeBackup = 1 AND @RetainHours IS NOT NULL
BEGIN;
BEGIN TRY
EXEC dbo.BackupDatabases_Cleanup @BackupDir=@BackupDir,@FileNamePattern=@FileNamePattern,@RetainHours=@RetainHours,@Debug=@Debug;
END TRY
BEGIN CATCH
SELECT @ErrorMessage = 'BackupDatabases_Cleanup Error:' + ERROR_MESSAGE(),
@ErrorSeverity = 10,
@ErrorState = 1,
@ErrorCount = @ErrorCount + 1
-- Log error (Severity of 10 won't cause agent job to fail)
RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH LOG
END CATCH
END;
IF @Debug=1
BEGIN;
PRINT @BackupCommand;
END;
ELSE
BEGIN;
BEGIN TRY
-- Run backup
EXEC sp_executesql @BackupCommand;
END TRY
BEGIN CATCH
-- Re-raise error and continue processing without verify or removing backup files
SELECT @ErrorMessage = 'BackupDatabases Error:' + ERROR_MESSAGE(),
@ErrorSeverity = 10,
@ErrorState = 1,
@ErrorCount = @ErrorCount+1;
-- Log error (Severity of 10 won't cause agent job to fail)
RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH LOG;
FETCH NEXT FROM cBackup INTO @Database;
CONTINUE;
END CATCH
END;
IF @Verify=1
BEGIN; -- Perform a verify on the backup if specified
SET @BackupCommand = 'RESTORE VERIFYONLY FROM ' + @BackupLocations;
IF @CheckSum=1
BEGIN;
SET @BackupCommand = @BackupCommand + ' WITH CHECKSUM';
END;
IF @Debug=1
BEGIN;
PRINT @BackupCommand;
END;
ELSE
BEGIN;
BEGIN TRY
-- Run verify
EXEC sp_executesql @BackupCommand;
END TRY
BEGIN CATCH
-- Re-raise error and continue processing without removing backup files
SELECT @ErrorMessage = 'BackupDatabases Error:' + ERROR_MESSAGE(),
@ErrorSeverity = 10,
@ErrorState = 1,
@ErrorCount = @ErrorCount + 1;
-- Log error (Severity of 10 won't cause agent job to fail)
RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH LOG;
FETCH NEXT FROM cBackup INTO @Database;
CONTINUE;
END CATCH
END;
END;
-- Removes old backups for this database
IF @DeleteBeforeBackup = 0 AND @RetainHours IS NOT NULL
BEGIN;
BEGIN TRY
EXEC dbo.BackupDatabases_Cleanup @BackupDir=@BackupDir,@FileNamePattern=@FileNamePattern,@RetainHours=@RetainHours,@Debug=@Debug;
END TRY
BEGIN CATCH
SELECT @ErrorMessage = 'BackupDatabases_Cleanup Error:' + ERROR_MESSAGE(),
@ErrorSeverity = 10,
@ErrorState = 1,
@ErrorCount = @ErrorCount + 1;
-- Log error (Severity of 10 won't cause agent job to fail)
RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH LOG;
END CATCH
END;
FETCH NEXT FROM cBackup INTO @Database;
END;
CLOSE cBackup;
DEALLOCATE cBackup;
IF @ErrorCount >0
BEGIN
SELECT @ErrorMessage ='One or more errors occurred during the backup. Please see the previous messages for details',
@ErrorSeverity = 11,
@ErrorState = 1;
RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState)
END
GO
CREATE PROC [dbo].[BackupDatabases_Cleanup](
-- Backup folder
@BackupDir NVARCHAR(MAX),
-- FileName pattern (Excluding the date)
-- e.g. SERVER.DATABASENAME.FULL.
@FileNamePattern NVARCHAR(1024),
-- Number of hours to keep backup
-- e.g. 24 = Keep backups for 1 day, 168 = Keep backups for 7 days (24*7)
@RetainHours INT,
-- Option to see what commands would be run without deleting any files.
@Debug BIT = 0
)
AS
/*
Created By: David Wiseman
Date: 2009-12-01
Website: http://www.wisesoft.co.uk
Description:
Used by dbo.BackupDatabases stored procedure to cleanup old backup files
Requires SQL CLR IO Utility and dbo.SplitString function:
http://www.wisesoft.co.uk/articles/sql_server_clr_io_utility.aspx
http://www.wisesoft.co.uk/scripts/t-sql_cte_split_string_function.aspx
*/
IF @RetainHours IS NOT NULL
BEGIN -- Remove old backup files if specified
DECLARE @BackupPath NVARCHAR(1024);
DECLARE @BackupLocation NVARCHAR(1024)
DECLARE cBackupLocations CURSOR FAST_FORWARD FOR
SELECT DISTINCT value as BackupLocation
FROM dbo.fnSplitString(@BackupDir,'|')
OPEN cBackupLocations
FETCH NEXT FROM cBackupLocations INTO @BackupLocation
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cBackupFiles CURSOR FAST_FORWARD FOR
SELECT [Path]
FROM dbo.SQLIO_fnGetFiles(@BackupLocation,'*.BAK',0) -- Get all files with the extension "BAK" from the backup directory
WHERE [file_name] LIKE @FileNamePattern + '%' -- Only filenames with the same pattern. e.g. SERVER.DATABASENAME.FULL.
AND modified_time_utc < DATEADD(HH,-@RetainHours,GETUTCDATE()); -- Only files older than @RetainHours hours are deleted
OPEN cBackupFiles;
FETCH NEXT FROM cBackupFiles INTO @BackupPath;
WHILE @@FETCH_STATUS = 0
BEGIN;
IF @Debug=1
BEGIN;
PRINT 'EXEC dbo.SQLIO_DeleteFile @Path=' + QUOTENAME(@BackupPath,'''');
END;
ELSE
BEGIN;
-- Delete backup file
EXEC dbo.SQLIO_DeleteFile @Path=@BackupPath;
END;
FETCH NEXT FROM cBackupFiles INTO @BackupPath;
END;
CLOSE cBackupFiles;
DEALLOCATE cBackupFiles;
FETCH NEXT FROM cBackupLocations INTO @BackupLocation
END
CLOSE cBackupLocations
DEALLOCATE cBackupLocations
END;
GO