stored proc for removing old backups inside

joshmurrahjoshmurrah Posts: 24
edited August 23, 2005 2:43AM in SQL Backup Previous Versions
I wrote this stored proc to remove all old backups, keeping only what's needed for one full restore... we have pretty tight space limitations, due to the size of some of the databases we run, and SQL Backup's built-in ERASEFILES option isn't too smart (can remove all full's when ran with a diff for example)... Hopefully somebody can get some use out of this. This is installed as part of our standard backup/maint. package, in SQL Server Agent, in a job step after the full/diff backups.
CREATE PROCEDURE pr_maint_ArchiveBackups
	@Database	SYSNAME,
	@BackupPath	VARCHAR(255),
	@BackupType 	VARCHAR(255)
AS
/*
Written by Josh Murrah, [email protected]
This stored procedure will take care of doing the housekeeping
on old/un-needed backups... it assumes the following:

1)  You only want one good set of backups.  There's no provision here for multiple sets.
2)  You have the database name and type (FULL, LOG, DIFF) , both surrounded with underscores 
    somewhere in the filename, usually with _<DATABASE>_<TYPE>_ in the file name 
    format under Options in Red-Gate SQL Backup GUI and doing <AUTO> during backup for the 
    filename specification... I use: <SERVER>_<DATABASE>_<TYPE>_<DATETIME yyyymmddhhmm>
3)  You backup each server/instance to a seperate folder, or at least have unique DB names.
4)  This does work with UNC paths, just make sure Red-Gate has permissions to do it first.
    In this case, you'd need to do a NET USE \\server\share passwd /user:xxx /persistent:yes 
    at some point to get the Red-Gate software working, this uses the same stored credintials.
*/
SET NOCOUNT ON

DECLARE @Sql VARCHAR(255)
DECLARE @LatestBackupFileId INT
DECLARE @counter INT
DECLARE @output VARCHAR(255)
DECLARE @ErrorMsg VARCHAR(255)

CREATE	TABLE #filetable (
	Ident	INT IDENTITY(1, 1),
	line	VARCHAR(128))
CREATE TABLE #delcommand (
	Ident	INT IDENTITY(1, 1),
	command	VARCHAR(255))
CREATE TABLE #errors (
	result	VARCHAR(255))

--check backup path
SELECT @Sql='dir '[email protected]
INSERT INTO #errors(result) EXEC master..xp_cmdshell @Sql
IF (SELECT COUNT(*) FROM #errors WHERE result LIKE '%File Not Found%') > 0
	BEGIN
	SET @ErrorMsg = 'Backup path '[email protected]+' does not exist'
	GOTO pr_maint_ArchiveBackups_ERROR
	END
TRUNCATE TABLE #errors

--get a file listing for the Database in question, from the backup path
SELECT @Sql='dir /b /o:-d '[email protected]+'\*_'[email protected]+'_*.sqb'
INSERT INTO #filetable(line) EXEC master..xp_cmdshell @Sql

--error checking for file list
IF @output <> 0
	BEGIN
	SET @ErrorMsg = 'error executing '[email protected]
	GOTO pr_maint_ArchiveBackups_ERROR
	END

--last line in a dir/b command comes back NULL
DELETE FROM #filetable where line IS NULL

--get the latest full backup file for the database in question and build deletion commands
IF @BackupType='FULL' 
	BEGIN
		SELECT @LatestBackupFileId=(SELECT TOP 1 Ident FROM #filetable
		WHERE line LIKE ('%'[email protected]+'%') AND line LIKE '%_FULL_%' ORDER BY Ident)
		INSERT INTO #delcommand(command) SELECT 'del '[email protected]+'\'+line FROM #filetable 
		WHERE (Ident > @LatestBackupFileId AND line LIKE '%_LOG_%') OR 
			(Ident > @LatestBackupFileId AND line LIKE '%_DIFF_%') OR
			(Ident > @LatestBackupFileId AND line LIKE '%_FULL_%')
	END
	ELSE
	BEGIN
		SELECT @LatestBackupFileId=(SELECT TOP 1 Ident FROM #filetable
		WHERE line LIKE ('%'[email protected]+'%') AND line LIKE '%_DIFF_%' ORDER BY Ident)
		INSERT INTO #delcommand(command) SELECT 'del '[email protected]+'\'+line FROM #filetable 
		WHERE (Ident > @LatestBackupFileId AND line LIKE '%_LOG_%')
	END

--loop through each command and execute.
SELECT @counter=1
WHILE @counter <= (SELECT MAX(Ident) FROM #delcommand)
	BEGIN
	SELECT @Sql=command FROM #delcommand WHERE [email protected]
	INSERT INTO #errors(result) EXEC @output=master..xp_cmdshell @Sql
	IF @output <> 0
		BEGIN
		SET @ErrorMsg = 'error executing '[email protected]
		GOTO pr_maint_ArchiveBackups_ERROR
		END
	SELECT @[email protected]+1
	END

IF (SELECT COUNT(*) FROM #errors WHERE result IS NOT NULL) > 0
	BEGIN
	SET @ErrorMsg = 'Unable to delete all old backups for [' + @Database+'].'
	GOTO pr_maint_ArchiveBackups_ERROR
	END

--no errors, so skip to cleanup
GOTO pr_maint_ArchiveBackups_EXIT

--if there was an error, jump to here
pr_maint_ArchiveBackups_ERROR:
RAISERROR(@ErrorMsg, 16, 1)

pr_maint_ArchiveBackups_EXIT:
--cleanup
DROP TABLE #errors
DROP TABLE #delcommand
DROP TABLE #filetable
-Josh Murrah

Comments

Sign In or Register to comment.