-- Full Backup
DECLARE @DBName VARCHAR(128) = 'Diablo',
@Folder VARCHAR(256) = 'H:\Backup\DiabloLogShipping\',
@FilesToKeep INT = 1 ;
WITH OldFiles
AS (SELECT @Folder + Name AS PATH,
ROW_NUMBER() OVER (ORDER BY CreateDate DESC) AS RowNum
FROM Util.FS.GetDirectoryInfo(@Folder, @DBName + '_Full_*.bak') d)
SELECT *
FROM OldFiles o
CROSS APPLY Util.[FS].[FileDelete](PATH) fd
WHERE RowNum > @FilesToKeep
-- Differential Backup
DECLARE @DBName VARCHAR(128) = 'Diablo',
@Folder VARCHAR(256) = 'H:\Backup\DiabloLogShipping\',
@FilesToKeep INT = 4 ;
WITH OldFiles
AS (SELECT @Folder + Name AS PATH,
k.CreateDate,
z.CreateDate AS FullBackupDate,
ROW_NUMBER() OVER (ORDER BY k.CreateDate DESC) AS RowNum
FROM Util.FS.GetDirectoryInfo(@Folder, @DBName + '_Differential_*.bak') k
OUTER APPLY (SELECT TOP 1 CreateDate
FROM Util.FS.GetDirectoryInfo (@Folder, @DBName + '_Full_*.bak')) z)
SELECT *
FROM OldFiles
CROSS APPLY Util.[FS].[FileDelete](PATH) fd
WHERE FullBackupDate > CreateDate
OR RowNum > @FilesToKeep