Teguh Triharto Learning Center: .::: Create Script Backup Daily Full, Differential, Log SQL Server Using T-SQL with Record to Table with Size Backup and using insert table client and create store procedure sp_backup_full, sp_backup_log, exec sp_backup

1. enable sqlcmd

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

2. create table record

use dbatools
go

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

delete from dbatools..BackupLogStatus
select * from dbatools..BackupLogStatus

3. create table tbl_clients
use dbatools
go

CREATE TABLE tbl_clients (
    client_id NVARCHAR(20) PRIMARY KEY, -- ID unik dalam format id-client-XX
    client_name NVARCHAR(255) NOT NULL, -- Nama client
    dbname NVARCHAR(255) NOT NULL UNIQUE, -- Nama database client
    created_at DATETIME DEFAULT GETDATE() -- Timestamp pembuatan data
);

4. sample insert client table

INSERT INTO dbatools..tbl_clients (client_id, client_name, dbname)
VALUES
    ('id-client-01', 'Client A', 'db_client_a'),
    ('id-client-02', 'Client B', 'db_client_b'),
    ('id-client-03', 'Client C', 'db_client_c'),
    ('id-client-04', 'Client D', 'db_client_d');

INSERT INTO dbatools..tbl_clients (client_id, client_name, dbname)
VALUES ('id-client-22', 'Client Teguh', 'teguhth');

5. check info or if want to delete
select * from dbatools..tbl_clients
select * from dbatools..BackupLogStatus order by BackupDate desc

--- delete from dbatools..BackupLogStatus
--- delete from dbatools..tbl_clients
--- delete from dbatools..tbl_clients where client_id='id-client-04'

6. combine sys.databases with tbl_client using sub query

SELECT name
FROM sys.databases
WHERE name IN (SELECT dbname FROM dbatools..tbl_clients);

7. create sp_backup_full_custom

USE [dbatools]
GO

/****** Object: StoredProcedure [dbo].[sp_backup_full_custom]    Script Date: 3/5/2025 1:25:42 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_backup_full_custom]
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 IN (SELECT dbname FROM dbatools..tbl_clients);

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

8. create sp_backup_log_custom

USE [dbatools]
GO

/****** Object: StoredProcedure [dbo].[sp_backup_log_custom]    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_custom]
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 IN (SELECT dbname FROM dbatools..tbl_clients);

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

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

9. create sp_backup_diff_custom

USE [dbatools]
GO

/****** Object: StoredProcedure [dbo].[sp_backup_diff_custom]    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_custom]
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 IN (SELECT dbname FROM dbatools..tbl_clients);

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.bak'

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

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

10. execute sp backup

exec sp_backup_full_custom
exec sp_backup_log_custom
exec sp_backup_diff_custom

11. check record table

select * from dbatools..tbl_clients
select * from dbatools..BackupLogStatus order by BackupDate desc