Archive for December 26th, 2016
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
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.