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

 

1. enable sqlcmd

EXEC sp_configure 'show advanced options', 1;
        RECONFIGURE;
        EXEC sp_configure 'xp_cmdshell', 1;
        RECONFIGURE;
        
2. create table record

CREATE TABLE BackupLogStatus (
    BackupDate DATETIME,
    DatabaseName NVARCHAR(255),
    BackupType NVARCHAR(50),
    Status NVARCHAR(50),
    BackupFile NVARCHAR(500),
    BackupSizeMB BIGINT  -- Ukuran file dalam byte
);

3. create store procedure backup full

USE [dbatools]
GO

/****** Object:  StoredProcedure [dbo].[sp_backup_full]    Script Date: 3/3/2025 1:30:00 PM ******/
-- =============================================  
-- Author: Teguh Triharto
-- Create date:  
-- Description: Script Backup Database
-- Website : https://www.linkedin.com/in/teguhth"
-- =============================================  

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[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)
DECLARE @BackupSize BIGINT
DECLARE @BackupSizeMB DECIMAL(18,2)  -- Ukuran dalam MB

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

        -- Ambil ukuran file backup setelah selesai
        DECLARE @FileSizeCmd NVARCHAR(1000)
        SET @FileSizeCmd = 'powershell -command "(Get-Item \"' + @BackupFile + '\").length / 1MB"'
        CREATE TABLE #FileSizeTable (FileSizeMB DECIMAL(18,2))
        INSERT INTO #FileSizeTable EXEC xp_cmdshell @FileSizeCmd
        SELECT TOP 1 @BackupSizeMB = FileSizeMB FROM #FileSizeTable WHERE FileSizeMB IS NOT NULL
        DROP TABLE #FileSizeTable

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

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

    FETCH NEXT FROM db_cursor INTO @DatabaseName  
END  

-- Tutup cursor
CLOSE db_cursor  
DEALLOCATE db_cursor
END
GO

4. create store procedure backup log

USE [dbatools]
GO

/****** Object:  StoredProcedure [dbo].[sp_backup_log]    Script Date: 3/3/2025 1:30:58 PM ******/

-- =============================================  
-- Author: Teguh Triharto
-- Create date:  
-- Description: Script Backup Database
-- Website : https://www.linkedin.com/in/teguhth"
-- =============================================  

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[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)
DECLARE @BackupSize BIGINT
DECLARE @BackupSizeMB DECIMAL(18,2)  -- Ukuran dalam MB

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

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

        -- Ambil ukuran file backup setelah selesai
        DECLARE @FileSizeCmd NVARCHAR(1000)
        SET @FileSizeCmd = 'powershell -command "(Get-Item \"' + @BackupFile + '\").length / 1MB"'
        CREATE TABLE #FileSizeTable (FileSizeMB DECIMAL(18,2))
        INSERT INTO #FileSizeTable EXEC xp_cmdshell @FileSizeCmd
        SELECT TOP 1 @BackupSizeMB = FileSizeMB FROM #FileSizeTable WHERE FileSizeMB IS NOT NULL
        DROP TABLE #FileSizeTable

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

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

    FETCH NEXT FROM db_cursor INTO @DatabaseName  
END  

-- Tutup cursor
CLOSE db_cursor  
DEALLOCATE db_cursor
END
GO

5. create store procedure backup Differential

USE [dbatools]
GO

/****** Object:  StoredProcedure [dbo].[sp_backup_diff]    Script Date: 3/3/2025 1:32:35 PM ******/
-- =============================================  
-- Author: Teguh Triharto
-- Create date:  
-- Description: Script Backup Database
-- Website : https://www.linkedin.com/in/teguhth"
-- =============================================  


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

    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)
DECLARE @BackupSize BIGINT
DECLARE @BackupSizeMB DECIMAL(18,2)  -- Ukuran dalam MB

-- 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 DIFFERENTIAL, COMPRESSION, FORMAT, STATS = 10;  

        -- Ambil ukuran file backup setelah selesai
        DECLARE @FileSizeCmd NVARCHAR(1000)
        SET @FileSizeCmd = 'powershell -command "(Get-Item \"' + @BackupFile + '\").length / 1MB"'
        CREATE TABLE #FileSizeTable (FileSizeMB DECIMAL(18,2))
        INSERT INTO #FileSizeTable EXEC xp_cmdshell @FileSizeCmd
        SELECT TOP 1 @BackupSizeMB = FileSizeMB FROM #FileSizeTable WHERE FileSizeMB IS NOT NULL
        DROP TABLE #FileSizeTable

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

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

    FETCH NEXT FROM db_cursor INTO @DatabaseName  
END  

-- Tutup cursor
CLOSE db_cursor  
DEALLOCATE db_cursor
END;
GO

6. Run backup script

exec sp_backup_full
exec sp_backup_log
exec sp_backup_diff

7. result
 

8. check info table
select * from dbatools..BackupLogStatus

select * from dbatools..BackupLogStatus
where databasename ='AdventureWorks2022' or databasename='teguhth'
order by DatabaseName, BackupDate desc


select * from dbatools..BackupLogStatus
where databasename ='AdventureWorks2022' or databasename='teguhth' or databasename='teguhth_dwh'
order by DatabaseName ,BackupDate desc
---delete from  dbatools..BackupLogStatus