Archive for December, 2016

Number of copies based retention for database backups

Ola Hallengren’s DatabaseBackup stored procedure implements retention of database backups based on the age of backups in hours. For example, if you specify the @CleanupTime parameter as 24 then when the next backup is run if the existing backup is older than 24 hours it will be deleted. If you want to retain two backups you would specify 48.

For certain cases a more natural way of specifying retention would be through number of copies. The attached version of Ola’s DatabaseBackup stored procedure implements this via the @CleanupCopies and @MirrorCleanupCopies parameters. The modified version of Ola’s stored procedure calls dbo.CleanupCopies to do the actual deletions:


CREATE PROCEDURE [dbo].[CleanupCopies]
@DirectoryPath nvarchar(4000),
@CleanupCopies int,
@NumberOfFiles int,
@NumberOfDirectories int
AS
BEGIN

SET NOCOUNT ON

DECLARE @RetainFiles int = @CleanupCopies * @NumberOfFiles / @NumberOfDirectories,
@CurrentFileID int,
@CurrentFileName nvarchar(4000),
@ReturnCode int = 0

DECLARE @CurrentFiles TABLE (ID int IDENTITY (1,1) PRIMARY KEY NOT NULL,
FileName varchar(4000) NULL,
Depth int NULL,
IsFile int NULL,
CleanupCompleted bit NULL,
CleanupOutput int NULL)

INSERT @CurrentFiles (FileName,Depth,IsFile)
EXEC [master].dbo.xp_dirtree @DirectoryPath,0,1

SET @ReturnCode = @@ERROR

IF @ReturnCode <> 0 RETURN @ReturnCode

DELETE FROM @CurrentFiles
WHERE ID IN (
SELECT TOP(@RetainFiles) ID
FROM @CurrentFiles
ORDER BY FileName DESC
)

UPDATE @CurrentFiles
SET CleanupCompleted = 0,
CleanupOutput = 0

-- Do the deletions
WHILE EXISTS (SELECT * FROM @CurrentFiles WHERE CleanupCompleted = 0)
BEGIN
SELECT TOP 1 @CurrentFileID = ID,
@CurrentFileName = @DirectoryPath + N'\' + FileName
FROM @CurrentFiles
WHERE CleanupCompleted = 0
ORDER BY ID ASC

EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, @CurrentFileName

UPDATE @CurrentFiles
SET CleanupCompleted = 1,
CleanupOutput = @ReturnCode
WHERE ID = @CurrentFileID

END

SELECT @ReturnCode = ISNULL(MAX(CleanupOutput),0) FROM @CurrentFiles

RETURN @ReturnCode

END

Caveats:

Retention based on number of copies is not available for transaction log backups. Retention based on age is best for these.

It only caters for native backups (ie not implemented for LiteSpeed, Red Gate and Idera backups). The reason being the solution makes use of xp_delete_file which only handles native backups.

 

Leave a comment