CleanupTime not deleted backup files for databases that no longer exist

[jrbilodeau]

Description of the issue
I have configured my FULL and DIFFERENTIAL backup jobs to clean up files older that 168 jours, but have noticed that when a database is deleted from SQL Server, the backup files never get removed even after the 168 hours has passed. Files for existing Databases are however being cleaned up approriately.

It would be great if there was a way to cleanup backup files for old databases that were deleted.

SQL Server version and edition
Microsoft SQL Server 2017 (RTM-CU21) (KB4557397) - 14.0.3335.7 (X64)

Version of the script
Version: 2020-01-26 14:06:53

What command are you executing?
EXECUTE [dbo].[DatabaseBackup]
@databases = 'USER_DATABASES',
@Directory = N'I:\SQL_Maintenance\Backups',
@BackupType = 'FULL',
@verify = 'Y',
@Cleanuptime = 168,
@compress = 'Y',
@Checksum = 'Y',
@LogToTable = 'Y'

EXECUTE [dbo].[DatabaseBackup]
@databases = 'USER_DATABASES',
@Directory = N'I:\SQL_Maintenance\Backups',
@BackupType = 'DIFF',
@verify = 'Y',
@Cleanuptime = 168,
@compress = 'Y',
@Checksum = 'Y',
@LogToTable = 'Y'

What output are you getting?

[Directory]

bruh ffs how hard is it to use a code block stop pinging me

[rjk]

I agree this would be useful.

I understand why it doesn't do it, but initially I also assumed it'd clean up the backups it took, not just the ones for databases that exist now. Otherwise it could be nice to mention in the docs for @CleanupTime

[rjk]

Here's a script I wrote to do this. It deletes old BAK and TRN files from folders where there's no matching database.

Disclaimer: It's probably buggy, assumes the default folder structure, has hard-coded values like root directory, directory separator, cleanup time, assumes TRN files for transaction log files, doesn't delete empty folders, ... and probably many other problems, but maybe useful for someone:

set nocount on 

-- Cleanup time is 7 days - for BOTH log and full backups
declare @CleanupDate datetime = dateadd( day, -7, getDate() )  
-- print @CleanupDate

declare @CleanupDirectoryRoot nvarchar(100)
select @CleanupDirectoryRoot = 'Y:\' + cast( SERVERPROPERTY('MachineName') as nvarchar(max) ) + '$' + cast( SERVERPROPERTY('InstanceName') as nvarchar(max) ) + '\'
-- print @CleanupDirectoryRoot

-- Find all the db subfolders
create table #directoryTree ( id int identity(1,1), subdirectory nvarchar(512), depth int) 
insert #directoryTree
exec sys.xp_dirtree @CleanupDirectoryRoot, 1, 0 

-- Take just the ones without a matching database 
declare @CleanupDirectories table ( FullPath nvarchar(1000) ) 

insert into @CleanupDirectories ( FullPath )  
select @CleanupDirectoryRoot + subdirectory + '\' 
from #directoryTree dt
left join sys.databases db on dt.subdirectory = db.name 
where db.database_id is null 

drop table #directoryTree

-- Delete all old BAK and TRN files from them 
declare @CleanupDirectory nvarchar(1000), @ReturnCode int 

while exists ( select * from @CleanupDirectories ) begin 

	select top (1) @CleanupDirectory = FullPath from @CleanupDirectories 

	print 'deleting BAK files older than ' +  CONVERT(nvarchar(19),@CleanupDate,126) + ' from ' + @CleanupDirectory
	exec @ReturnCode = sys.xp_delete_file 0, @CleanupDirectory, 'BAK', @CleanupDate, 1
	if @ReturnCode <> 0 RAISERROR('Error deleting files.', 16, 1)

	print 'deleting TRN files older than ' +  CONVERT(nvarchar(19),@CleanupDate,126) + ' from ' + @CleanupDirectory
	exec @ReturnCode = sys.xp_delete_file 0, @CleanupDirectory, 'TRN', @CleanupDate, 1
	if @ReturnCode <> 0 RAISERROR('Error deleting files.', 16, 1)

	-- We could now look for empty folders and delete them but ... 
	-- It's hard to write sql to do this completely safely, and clearly I'm lazy, 
	-- so I'll just leave those empty folders :(

	delete from @CleanupDirectories where FullPath = @CleanupDirectory
		
end 

print 'completed'