.::: Create Script Backup Daily Full, Differential, Log SQL Server Using T-SQL & Include delete file Folder :::.

 

1. Backup Full

--- ### Script Begin ### ---
DECLARE @DatabaseName NVARCHAR(255)
DECLARE @BackupPath NVARCHAR(500)
DECLARE @BackupFile NVARCHAR(500)

-- Set lokasi penyimpanan backup (ubah sesuai kebutuhan)
SET @BackupPath = 'C:\BackupDaily\'  

-- Cursor untuk mendapatkan semua database kecuali sistem database
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')  -- Hindari backup database sistem

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @DatabaseName  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    -- Set nama file backup
    SET @BackupFile = @BackupPath + @DatabaseName + '_'+ FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + '.bak'

    -- Perintah Backup
    PRINT 'Backing up: ' + @DatabaseName  
    BACKUP DATABASE @DatabaseName  
    TO DISK = @BackupFile  
    WITH INIT, COMPRESSION, FORMAT, STATS = 10  

    FETCH NEXT FROM db_cursor INTO @DatabaseName  
END  

-- Tutup cursor
CLOSE db_cursor  
DEALLOCATE db_cursor  

--- ### Script End ### ---
  

2. Backup Transaksi log

--- ### Script Begin ### ---

DECLARE @DatabaseName NVARCHAR(255)
DECLARE @BackupPath NVARCHAR(500)
DECLARE @BackupFile NVARCHAR(500)

-- Set lokasi penyimpanan backup (ubah sesuai kebutuhan)
SET @BackupPath = 'C:\BackupDaily\'  

-- Cursor untuk mendapatkan semua database kecuali sistem database
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')  -- Hindari backup database sistem

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @DatabaseName  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    -- Set nama file backup
    SET @BackupFile = @BackupPath + @DatabaseName + '_'+ FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + '.trn'

    -- Perintah Backup
    PRINT 'Backing up: ' + @DatabaseName  
    BACKUP LOG @DatabaseName  
    TO DISK = @BackupFile  
    -- WITH INIT, COMPRESSION, FORMAT, STATS = 10  
    WITH NOFORMAT, NOINIT,  NAME = N'teguhth-LOG Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
    FETCH NEXT FROM db_cursor INTO @DatabaseName  
END  

-- Tutup cursor
CLOSE db_cursor  
DEALLOCATE db_cursor  
--- ### Script End ### ---
 

3. berdasarkan folder

EXEC sp_configure 'show advanced options', 1;
        RECONFIGURE;
        EXEC sp_configure 'xp_cmdshell', 1;
        RECONFIGURE;

        
--- ### Script Begin ### ---        

DECLARE @DatabaseName NVARCHAR(255)
DECLARE @BackupBasePath NVARCHAR(500)
DECLARE @BackupFolder NVARCHAR(500)
DECLARE @BackupFile NVARCHAR(500)
DECLARE @Command NVARCHAR(1000)

-- Set lokasi penyimpanan backup utama (ubah sesuai kebutuhan)
SET @BackupBasePath = 'C:\BackupDaily\'  

-- Cursor untuk mendapatkan semua database kecuali sistem database
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')  -- Hindari backup database sistem

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @DatabaseName  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    -- Tentukan folder khusus untuk database saat ini
    SET @BackupFolder = @BackupBasePath + @DatabaseName + '\'

    -- Buat folder jika belum ada
    SET @Command = 'IF NOT EXIST "' + @BackupFolder + '" mkdir "' + @BackupFolder + '"'
    EXEC xp_cmdshell @Command, NO_OUTPUT  

    -- Set nama file backup
    SET @BackupFile = @BackupFolder + @DatabaseName + '_'+ FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + '.bak'

    -- Perintah Backup
    PRINT 'Backing up: ' + @DatabaseName + ' to ' + @BackupFile  
    BACKUP DATABASE @DatabaseName  
    TO DISK = @BackupFile  
    WITH INIT, COMPRESSION, FORMAT, STATS = 10  

    FETCH NEXT FROM db_cursor INTO @DatabaseName  
END  

-- Tutup cursor
CLOSE db_cursor  
DEALLOCATE db_cursor  
--- ### Script End ### ---
 

4. berdasarkan folder dan tanggal backup full

--- ### Script Begin ### ---
DECLARE @DatabaseName NVARCHAR(255)
DECLARE @BackupBasePath NVARCHAR(500)
DECLARE @BackupDateFolder NVARCHAR(500)
DECLARE @BackupFolder NVARCHAR(500)
DECLARE @BackupFile NVARCHAR(500)
DECLARE @Command NVARCHAR(1000)
DECLARE @DateFolder NVARCHAR(20)

-- Set lokasi penyimpanan backup utama (ubah sesuai kebutuhan)
SET @BackupBasePath = 'C:\BackupDaily\'  

-- Tentukan folder berdasarkan tanggal (YYYYMMDD)
SET @DateFolder = FORMAT(GETDATE(), 'yyyyMMdd')
SET @BackupDateFolder = @BackupBasePath + @DateFolder + '\'

-- Buat folder berdasarkan tanggal jika belum ada
SET @Command = 'IF NOT EXIST "' + @BackupDateFolder + '" mkdir "' + @BackupDateFolder + '"'
EXEC xp_cmdshell @Command, NO_OUTPUT  

-- Cursor untuk mendapatkan semua database kecuali sistem database
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')  -- Hindari backup database sistem

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @DatabaseName  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    -- Tentukan folder khusus untuk database di dalam folder tanggal
    SET @BackupFolder = @BackupDateFolder + @DatabaseName + '\'

    -- Buat folder database dalam folder tanggal jika belum ada
    SET @Command = 'IF NOT EXIST "' + @BackupFolder + '" mkdir "' + @BackupFolder + '"'
    EXEC xp_cmdshell @Command, NO_OUTPUT  

    -- Set nama file backup
    SET @BackupFile = @BackupFolder + @DatabaseName + '_'+ FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + '.bak'

    -- Perintah Backup
    PRINT 'Backing Up Full : ' + @DatabaseName + ' to ' + @BackupFile  
    BACKUP DATABASE @DatabaseName  
    TO DISK = @BackupFile  
    WITH INIT, COMPRESSION, FORMAT, STATS = 10  

    FETCH NEXT FROM db_cursor INTO @DatabaseName  
END  

-- Tutup cursor
CLOSE db_cursor  
DEALLOCATE db_cursor  
--- ### Script End ### ---
 

5. berdasarkan folder dan tanggal backup Log

--- ### Script Begin ### ---
DECLARE @DatabaseName NVARCHAR(255)
DECLARE @BackupBasePath NVARCHAR(500)
DECLARE @BackupDateFolder NVARCHAR(500)
DECLARE @BackupFolder NVARCHAR(500)
DECLARE @BackupFile NVARCHAR(500)
DECLARE @Command NVARCHAR(1000)
DECLARE @DateFolder NVARCHAR(20)

-- Set lokasi penyimpanan backup utama (ubah sesuai kebutuhan)
SET @BackupBasePath = 'C:\BackupDaily\'  

-- Tentukan folder berdasarkan tanggal (YYYYMMDD)
SET @DateFolder = FORMAT(GETDATE(), 'yyyyMMdd')
SET @BackupDateFolder = @BackupBasePath + @DateFolder + '\'

-- Buat folder berdasarkan tanggal jika belum ada
SET @Command = 'IF NOT EXIST "' + @BackupDateFolder + '" mkdir "' + @BackupDateFolder + '"'
EXEC xp_cmdshell @Command, NO_OUTPUT  

-- Cursor untuk mendapatkan semua database kecuali sistem database
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')  -- Hindari backup database sistem

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @DatabaseName  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    -- Tentukan folder khusus untuk database di dalam folder tanggal
    SET @BackupFolder = @BackupDateFolder + @DatabaseName + '\'

    -- Buat folder database dalam folder tanggal jika belum ada
    SET @Command = 'IF NOT EXIST "' + @BackupFolder + '" mkdir "' + @BackupFolder + '"'
    EXEC xp_cmdshell @Command, NO_OUTPUT  

    -- Set nama file backup
    SET @BackupFile = @BackupFolder + @DatabaseName + '_'+ FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + '.trn'

    -- Perintah Backup
    PRINT 'Backing Up Log : ' + @DatabaseName + ' to ' + @BackupFile  
    BACKUP LOG @DatabaseName  
    TO DISK = @BackupFile  
    WITH INIT, COMPRESSION, FORMAT, STATS = 10  

    FETCH NEXT FROM db_cursor INTO @DatabaseName  
END  

-- Tutup cursor
CLOSE db_cursor  
DEALLOCATE db_cursor  
 

6. berdasarkan folder dan tanggal backup Diffential

--- ### Script Begin ### ---
DECLARE @DatabaseName NVARCHAR(255)
DECLARE @BackupBasePath NVARCHAR(500)
DECLARE @BackupDateFolder NVARCHAR(500)
DECLARE @BackupFolder NVARCHAR(500)
DECLARE @BackupFile NVARCHAR(500)
DECLARE @Command NVARCHAR(1000)
DECLARE @DateFolder NVARCHAR(20)

-- Set lokasi penyimpanan backup utama (ubah sesuai kebutuhan)
SET @BackupBasePath = 'C:\BackupDaily\'  

-- Tentukan folder berdasarkan tanggal (YYYYMMDD)
SET @DateFolder = FORMAT(GETDATE(), 'yyyyMMdd')
SET @BackupDateFolder = @BackupBasePath + @DateFolder + '\'

-- Buat folder berdasarkan tanggal jika belum ada
SET @Command = 'IF NOT EXIST "' + @BackupDateFolder + '" mkdir "' + @BackupDateFolder + '"'
EXEC xp_cmdshell @Command, NO_OUTPUT  

-- Cursor untuk mendapatkan semua database kecuali sistem database
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')  -- Hindari backup database sistem

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @DatabaseName  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    -- Tentukan folder khusus untuk database di dalam folder tanggal
    SET @BackupFolder = @BackupDateFolder + @DatabaseName + '\'

    -- Buat folder database dalam folder tanggal jika belum ada
    SET @Command = 'IF NOT EXIST "' + @BackupFolder + '" mkdir "' + @BackupFolder + '"'
    EXEC xp_cmdshell @Command, NO_OUTPUT  

    -- Set nama file backup
    SET @BackupFile = @BackupFolder + @DatabaseName + '_'+ FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + '.diff'

    -- Perintah Backup
    PRINT 'Backing Up Differential : ' + @DatabaseName + ' to ' + @BackupFile  
    BACKUP DATABASE @DatabaseName  
    TO DISK = @BackupFile  
    WITH DIFFERENTIAL, COMPRESSION, FORMAT, STATS = 10;  

    FETCH NEXT FROM db_cursor INTO @DatabaseName  
END  

-- Tutup cursor
CLOSE db_cursor  
DEALLOCATE db_cursor  
--- ### Script End ### ---
 

7. TSQL Delete file & folder 7day
-- Menghapus file di C:\BackupDaily\ yang lebih dari 7 hari
EXEC xp_cmdshell 'forfiles /p "C:\BackupDaily" /s /m *.* /d -7 /c "cmd /c del /f /q @path"';

-- Menghapus subfolder yang lebih dari 7 hari
EXEC xp_cmdshell 'forfiles /p "C:\BackupDaily" /s /d -7 /c "cmd /c rmdir /s /q @path"';