.::: T-SQL Clean backup retention di SQL Server MSSQL :::.

  

correlation with http://teguhth.blogspot.com/2025/12/create-script-backup-all-database-full.html

1. create sp_backup .bat script for backup

### 10.10.10.200

sqlcmd -U user -P password -S 10.10.10.200 -d dbatools -Q "exec dbatools.[dbo].[sp_backup_full_basic]"
sqlcmd -U user -P password -S 10.10.10.200 -d dbatools -Q "exec dbatools.[dbo].[sp_backup_diff_basic]"
sqlcmd -U user -P password -S 10.10.10.200 -d dbatools -Q "exec dbatools.[dbo].[sp_backup_log_basic]"

2. create sp_cleanbackup.bat for sp_clean

sqlcmd -U user -P password -S 10.10.10.200 -d dbatools -Q "exec dbatools.[dbo].[sp_backup_cleanup_direct]"
 3. running sp_backup.bat

4. check result 
 

5. change LastWriteTime to testing only 

# for file

(Get-Item "C:\BackupDaily\20260224\AdventureWorks2022\Full_AdventureWorks2022_20260224_083734.bak").CreationTime = "2023-02-24 08:56:00"
(Get-Item "C:\BackupDaily\20260224\AdventureWorks2022\Full_AdventureWorks2022_20260224_083734.bak").LastWriteTime = "2023-02-24 08:56:00"

# for folder 

powershell -Command "Get-ChildItem 'C:\BackupDaily\20260224\teguhth\' -Recurse -File | ForEach-Object { $_.LastWriteTime = '2023-02-24 08:56:00' }"

6. running sp_cleanbackup.bat

7. check result 
 

8. if want running using cmd manual

forfiles /p "C:\BackupDaily" /s /d -7 /c "cmd /c del /q @path"

9. make task scheduler for sp_backup.bat & sp_cleanbackup.bat 

10. t-sql backup sql 2008 in 
correlation with http://teguhth.blogspot.com/2025/12/create-script-backup-all-database-full.html

11. t-sql for clean backup sp_backup_cleanup_direct

CREATE PROCEDURE [dbo].[sp_backup_cleanup_direct]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @BackupBasePath NVARCHAR(500) = 'C:\BackupDaily';
    DECLARE @RetentionDays  INT = 7;
    DECLARE @cmd NVARCHAR(2000);

    IF RIGHT(@BackupBasePath,1) = '\'
        SET @BackupBasePath = LEFT(@BackupBasePath, LEN(@BackupBasePath)-1);

    SET @cmd = 'forfiles /p "' + @BackupBasePath + 
               '" /s /d -' + CAST(@RetentionDays AS VARCHAR(5)) + 
               ' /c "cmd /c echo Deleting @path & del /q @path"';

    DECLARE @output TABLE (line NVARCHAR(4000));

    INSERT INTO @output
    EXEC master.dbo.xp_cmdshell @cmd;

    -- Hapus NULL & pesan ERROR bawaan forfiles
    DELETE FROM @output
    WHERE line IS NULL
       OR line LIKE 'ERROR:%';

    -- Jika ada file terhapus
    IF EXISTS (SELECT 1 FROM @output)
    BEGIN
        SELECT line AS [Deleted_Files]
        FROM @output;
    END
    ELSE
    BEGIN
        SELECT 'Tidak ada file yang lebih dari '
               + CAST(@RetentionDays AS VARCHAR)
               + ' hari.' AS [Info];
    END
END
GO

12. t-sql for clean backup sp_backup_cleanup

CREATE PROCEDURE [dbo].[sp_backup_cleanup]
(
    @BackupBasePath NVARCHAR(500),
    @RetentionDays  INT = 7
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @cmd NVARCHAR(2000);

    IF RIGHT(@BackupBasePath,1) = '\'
        SET @BackupBasePath = LEFT(@BackupBasePath, LEN(@BackupBasePath)-1);

    SET @cmd = 'forfiles /p "' + @BackupBasePath +
               '" /s /d -' + CAST(@RetentionDays AS VARCHAR(5)) +
               ' /c "cmd /c echo Deleting @path & del /q @path"';

    DECLARE @output TABLE (line NVARCHAR(4000));

    INSERT INTO @output
    EXEC master.dbo.xp_cmdshell @cmd;

    -- Hapus NULL & ERROR bawaan forfiles
    DELETE FROM @output 
    WHERE line IS NULL
       OR line LIKE 'ERROR:%';

    -- Jika ada file terhapus
    IF EXISTS (SELECT 1 FROM @output)
    BEGIN
        SELECT line AS Deleted_File
        FROM @output;
    END
    ELSE
    BEGIN
        SELECT 'Tidak ada file yang lebih dari '
               + CAST(@RetentionDays AS VARCHAR)
               + ' hari.' AS Info;
    END
END
GO