.::: Create Script Backup Daily Full, Differential, Log SQL Server Using T-SQL with Record to Table and create store procedure sp_backup_full, sp_backup_log, exec sp_backup_diff :::.

 

1. Create Table BackupLogStatus

CREATE TABLE BackupLogStatus (
    BackupDate DATETIME,
    DatabaseName NVARCHAR(255),
    BackupType NVARCHAR(50),
    Status NVARCHAR(50),
    BackupFile NVARCHAR(500)   
);
 2. create script 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)
DECLARE @BackupStatus NVARCHAR(50)

-- 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 Full
    PRINT 'Backing Up Full : ' + @DatabaseName + ' to ' + @BackupFile  

    BEGIN TRY
        -- Backup Database
        BACKUP DATABASE @DatabaseName  
        TO DISK = @BackupFile  
        WITH INIT, COMPRESSION, FORMAT, STATS = 10  


        -- Set status berhasil jika backup sukses
        SET @BackupStatus = 'Success'
    END TRY
    BEGIN CATCH
        -- Set status gagal jika ada error
        SET @BackupStatus = 'Failed'
    END CATCH

    -- Simpan status backup full ke dalam tabel BackupLogStatus
    INSERT INTO dbatools.dbo.BackupLogStatus (BackupDate, DatabaseName, BackupType, Status ,BackupFile)
    VALUES (GETDATE(),@DatabaseName, 'Full',@BackupStatus,@BackupFile)
    

    FETCH NEXT FROM db_cursor INTO @DatabaseName  
END  

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

3. Create 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)
DECLARE @BackupStatus NVARCHAR(50)

-- 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 LOG
    PRINT 'Backing Up LOG : ' + @DatabaseName + ' to ' + @BackupFile  

    BEGIN TRY
        -- Backup Database
        BACKUP LOG @DatabaseName  
        TO DISK = @BackupFile  
        WITH INIT, COMPRESSION, FORMAT, STATS = 10  

        -- Set status berhasil jika backup sukses
        SET @BackupStatus = 'Success'
    END TRY
    BEGIN CATCH
        -- Set status gagal jika ada error
        SET @BackupStatus = 'Failed'
    END CATCH

    -- Simpan status backup Log ke dalam tabel BackupLogStatus
    INSERT INTO dbatools.dbo.BackupLogStatus (BackupDate, DatabaseName, BackupType, Status ,BackupFile)
    VALUES (GETDATE(),@DatabaseName, 'LOG',@BackupStatus,@BackupFile)
    
    FETCH NEXT FROM db_cursor INTO @DatabaseName  
END  

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

4. create backup differential

--- ### 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)
DECLARE @BackupStatus NVARCHAR(50)

-- 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 DIFFERENTIAL
    PRINT 'Backing Up Differential : ' + @DatabaseName + ' to ' + @BackupFile  

    BEGIN TRY
        -- Backup Database
         
        BACKUP DATABASE @DatabaseName  
        TO DISK = @BackupFile  
        WITH DIFFERENTIAL, COMPRESSION, FORMAT, STATS = 10;


        -- Set status berhasil jika backup sukses
        SET @BackupStatus = 'Success'
    END TRY
    BEGIN CATCH
        -- Set status gagal jika ada error
        SET @BackupStatus = 'Failed'
    END CATCH

    -- Simpan status backup full ke dalam tabel BackupLogStatus
    INSERT INTO dbatools.dbo.BackupLogStatus (BackupDate, DatabaseName, BackupType, Status ,BackupFile)
    VALUES (GETDATE(),@DatabaseName, 'DIFF',@BackupStatus,@BackupFile)
    

    FETCH NEXT FROM db_cursor INTO @DatabaseName  
END  

-- Tutup cursor
CLOSE db_cursor  
DEALLOCATE db_cursor

5. create store procedure sp_backup_full

CREATE PROCEDURE sp_backup_full
AS
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)
    DECLARE @BackupStatus NVARCHAR(50)

    -- 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 Full
        PRINT 'Backing Up Full : ' + @DatabaseName + ' to ' + @BackupFile  

        BEGIN TRY
            -- Backup Database
            BACKUP DATABASE @DatabaseName  
            TO DISK = @BackupFile  
            WITH INIT, COMPRESSION, FORMAT, STATS = 10  

            -- Set status berhasil jika backup sukses
            SET @BackupStatus = 'Success'
        END TRY
        BEGIN CATCH
            -- Set status gagal jika ada error
            SET @BackupStatus = 'Failed'
        END CATCH

    -- Simpan status backup full ke dalam tabel BackupLogStatus
    INSERT INTO dbatools.dbo.BackupLogStatus (BackupDate, DatabaseName, BackupType, Status ,BackupFile)
    VALUES (GETDATE(),@DatabaseName, 'Full',@BackupStatus,@BackupFile)

        FETCH NEXT FROM db_cursor INTO @DatabaseName  
    END  

    -- Tutup cursor
    CLOSE db_cursor  
    DEALLOCATE db_cursor
END

6. create sp_backup_log

CREATE PROCEDURE sp_backup_log
AS
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)
    DECLARE @BackupStatus NVARCHAR(50)

    -- 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 Full
        PRINT 'Backing Up LOG : ' + @DatabaseName + ' to ' + @BackupFile  

        BEGIN TRY
            -- Backup Database
            BACKUP LOG @DatabaseName  
            TO DISK = @BackupFile  
            WITH INIT, COMPRESSION, FORMAT, STATS = 10  

            -- Set status berhasil jika backup sukses
            SET @BackupStatus = 'Success'
        END TRY
        BEGIN CATCH
            -- Set status gagal jika ada error
            SET @BackupStatus = 'Failed'
        END CATCH

    -- Simpan status backup Log ke dalam tabel BackupLogStatus
    INSERT INTO dbatools.dbo.BackupLogStatus (BackupDate, DatabaseName, BackupType, Status ,BackupFile)
    VALUES (GETDATE(),@DatabaseName, 'LOG',@BackupStatus,@BackupFile)

        FETCH NEXT FROM db_cursor INTO @DatabaseName  
    END  

    -- Tutup cursor
    CLOSE db_cursor  
    DEALLOCATE db_cursor
END

7. create sp_backup_diff

CREATE PROCEDURE sp_backup_diff
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @DatabaseName NVARCHAR(255);
    DECLARE @BackupBasePath NVARCHAR(500) = 'C:\BackupDaily\';
    DECLARE @BackupDateFolder NVARCHAR(500);
    DECLARE @BackupFolder NVARCHAR(500);
    DECLARE @BackupFile NVARCHAR(500);
    DECLARE @Command NVARCHAR(1000);
    DECLARE @DateFolder NVARCHAR(20);
    DECLARE @BackupStatus NVARCHAR(50);

    -- 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');

    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';

        PRINT 'Backing Up Differential : ' + @DatabaseName + ' to ' + @BackupFile;

        BEGIN TRY
            -- Backup Database
            BACKUP DATABASE @DatabaseName  
            TO DISK = @BackupFile  
            WITH DIFFERENTIAL, COMPRESSION, FORMAT, STATS = 10;

            -- Set status berhasil jika backup sukses
            SET @BackupStatus = 'Success';
        END TRY
        BEGIN CATCH
            -- Set status gagal jika ada error
            SET @BackupStatus = 'Failed';
        END CATCH

        -- Simpan status backup ke dalam   tabel BackupLogStatus
        INSERT INTO dbatools.db  

o.BackupLogStatus (BackupDate, DatabaseName, BackupType, Status, BackupFile)
        VALUES (GETDATE(), @DatabaseName, 'DIFF', @BackupStatus, @BackupFile);

        FETCH NEXT FROM db_cursor INTO @DatabaseName;
    END;

    -- Tutup cursor
    CLOSE db_cursor;
    DEALLOCATE db_cursor;
END;

8. Output backup

 

9. Check record table