Procedure Delete Backup History

USE tempdb;
GO

CREATE OR ALTER PROCEDURE usp_DeleteBackupHistory
(
    @DeleteLogDays INT = 30,
    @DeleteDiffDays INT = 30,
    @DeleteFullDays INT = 30, -- Number of days to retain full backups before considering deletion
    @AllDeleteDays INT = 365 -- First full backup for every month will be retained for AllDeleteDays
)
AS
BEGIN
    DECLARE @LogDeleteDate DATETIME;
    SET @LogDeleteDate = DATEADD(DAY, -@DeleteLogDays, GETDATE());

    DECLARE @DiffDeleteDate DATETIME;
    SET @DiffDeleteDate = DATEADD(DAY, -@DeleteDiffDays, GETDATE());

    DECLARE @AllDeleteDate DATETIME;
    SET @AllDeleteDate = DATEADD(DAY, -@AllDeleteDays, GETDATE());

    -- Temporary tables to store IDs for deletion
    CREATE TABLE #backup_set_id (backup_set_id INT);
    CREATE TABLE #restore_history_id (restore_history_id INT);
    CREATE TABLE #media_set_id (media_set_id INT);

    -- Insert IDs for log backups older than @DeleteLogDays
    INSERT INTO #backup_set_id
    SELECT backup_set_id
    FROM msdb..backupset WITH (NOLOCK)
    WHERE type = 'L'
    AND backup_finish_date < @LogDeleteDate;

    -- Insert IDs for differential backups older than @DeleteDiffDays
    INSERT INTO #backup_set_id
    SELECT backup_set_id
    FROM msdb..backupset WITH (NOLOCK)
    WHERE type = 'I'
    AND backup_finish_date < @DiffDeleteDate;

    -- Insert IDs for full backups older than @DeleteFullDays, excluding the first full backup of each month
    WITH FirstFullBackupOfMonth AS (
        SELECT 
            database_name,
            MIN(backup_finish_date) AS FirstBackupDate
        FROM 
            msdb..backupset WITH (NOLOCK)
        WHERE 
            type = 'D'
            AND backup_finish_date < DATEADD(DAY, -@DeleteFullDays, GETDATE())
        GROUP BY 
            database_name, 
            YEAR(backup_finish_date), 
            MONTH(backup_finish_date)
    )
    INSERT INTO #backup_set_id
    SELECT backup_set_id
    FROM msdb..backupset WITH (NOLOCK)
    WHERE type = 'D'
    AND backup_finish_date < DATEADD(DAY, -@DeleteFullDays, GETDATE())
    AND backup_finish_date NOT IN (
        SELECT FirstBackupDate
        FROM FirstFullBackupOfMonth
    );

    -- Insert restore history IDs related to the backup sets
    INSERT INTO #restore_history_id
    SELECT restore_history_id
    FROM msdb..restorehistory WITH (NOLOCK)
    WHERE backup_set_id IN (SELECT backup_set_id FROM #backup_set_id);

    -- Insert media set IDs related to the backup sets
    INSERT INTO #media_set_id
    SELECT media_set_id
    FROM msdb..backupset WITH (NOLOCK)
    WHERE backup_set_id IN (SELECT backup_set_id FROM #backup_set_id);

    -- Delete related records in the correct order
    DELETE FROM msdb..restorefile
    WHERE restore_history_id IN (SELECT restore_history_id FROM #restore_history_id);

    DELETE FROM msdb..restorefilegroup
    WHERE restore_history_id IN (SELECT restore_history_id FROM #restore_history_id);

    DELETE FROM msdb..restorehistory
    WHERE restore_history_id IN (SELECT restore_history_id FROM #restore_history_id);

    DELETE FROM msdb..backupfilegroup
    WHERE backup_set_id IN (SELECT backup_set_id FROM #backup_set_id);

    DELETE FROM msdb..backupfile
    WHERE backup_set_id IN (SELECT backup_set_id FROM #backup_set_id);

    DELETE FROM msdb..backupmediafamily
    WHERE media_set_id IN (SELECT media_set_id FROM #media_set_id)
    AND ((SELECT COUNT(*) FROM msdb..backupset WITH (NOLOCK) WHERE media_set_id = msdb..backupmediafamily.media_set_id) = 0);

    DELETE FROM msdb..backupmediaset
    WHERE media_set_id IN (SELECT media_set_id FROM #media_set_id)
    AND ((SELECT COUNT(*) FROM msdb..backupset WITH (NOLOCK) WHERE media_set_id = msdb..backupmediaset.media_set_id) = 0);

    DELETE FROM msdb..backupset
    WHERE backup_set_id IN (SELECT backup_set_id FROM #backup_set_id);

    -- Clean up old backup history
    EXEC msdb..sp_delete_backuphistory @oldest_date = @AllDeleteDate;

    -- Clean up temporary tables
    DROP TABLE #backup_set_id;
    DROP TABLE #restore_history_id;
    DROP TABLE #media_set_id;
END
GO

-- Execute the stored procedure
EXEC usp_DeleteBackupHistory;