.::: Store Procedure backup database & database dmart or other in MSSQL SQL Server 2025 :::.

  

1. check store procedure

SELECT 
    db_name() as dbname,name AS ProcedureName,
    SCHEMA_NAME(schema_id) AS SchemaName,
    create_date,
    modify_date
FROM 
    sys.procedures
ORDER BY 
    name;
    
-- or 

SELECT 
    db_name() as dbname,name AS ProcedureName,
    SCHEMA_NAME(schema_id) AS SchemaName,
    create_date,
    modify_date
FROM 
    sys.procedures where name='sp_backupdev' 
ORDER BY 
    name;
  

2. enable sqlcmd

EXEC sp_configure 'show advanced options', 1;
        RECONFIGURE;
        EXEC sp_configure 'xp_cmdshell', 1;
        RECONFIGURE;
        
3. run all for testing

exec sp_backupdev 'dbatools'

WAITFOR DELAY '00:00:10';

exec sp_backupdev 'teguhth';

WAITFOR DELAY '00:00:10';

exec sp_backupdev teguhth;

WAITFOR DELAY '00:00:10';

exec sp_backupdev 'teguhth',1

WAITFOR DELAY '00:00:10';

exec sp_backupdev 'teguhth',2
 

4. Script store procedure to backupdev

CREATE OR ALTER PROCEDURE sp_backupdev
    @dbname sysname,
    @backup_option int = 2
-- =============================================  
-- Author: Teguh Triharto
-- Create date:  
-- Description: IT Database Administrator
-- Website : https://www.linkedin.com/in/teguhth"
-- =============================================  

AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @dbname_dmart sysname,
        @folder_date varchar(8),
        @file_date varchar(17),
        @backup_folder varchar(500),
        @backup_file1 varchar(500),
        @backup_file2 varchar(500),
        @cmd varchar(1000),
        @dmart_exists bit = 0;

EXEC sp_configure 'show advanced options', 1;
        RECONFIGURE;
        EXEC sp_configure 'xp_cmdshell', 1;
        RECONFIGURE;
    ------------------------------------------------------------
    -- Database kedua
    ------------------------------------------------------------
    SET @dbname_dmart = @dbname + '_dmart';

    ------------------------------------------------------------
    -- Validasi pilihan
    --
    -- 1 = backup database utama saja
    -- 2 = backup database utama + _dmart
    ------------------------------------------------------------
    IF @backup_option NOT IN (1, 2)
    BEGIN
        RAISERROR(
            'Pilihan backup tidak valid. Gunakan 1 atau 2.',
            16,
            1
        );
        RETURN;
    END;

    ------------------------------------------------------------
    -- Validasi database utama
    ------------------------------------------------------------
    IF DB_ID(@dbname) IS NULL
    BEGIN
        RAISERROR(
            'Database %s tidak ditemukan.',
            16,
            1,
            @dbname
        );
        RETURN;
    END;

    ------------------------------------------------------------
    -- Cek database _dmart
    ------------------------------------------------------------
    IF DB_ID(@dbname_dmart) IS NOT NULL
    BEGIN
        SET @dmart_exists = 1;
    END;

    ------------------------------------------------------------
    -- Format tanggal
    --
    -- Folder:
    -- C:\backup\manual\260812
    --
    -- File:
    -- dbname_26-08-12-094125.bak
    ------------------------------------------------------------

    SET @folder_date =
        FORMAT(GETDATE(), 'yyyyMMdd');

    SET @file_date =
        FORMAT(GETDATE(), 'yyyyMMdd-HHmmss'); 
/*** sql 2008

SET @folder_date = CONVERT(varchar(8), GETDATE(), 112);

SET @file_date =
    CONVERT(varchar(10), GETDATE(), 120) + '-' +
    REPLACE(CONVERT(varchar(8), GETDATE(), 108), ':', ''); ***/

/*** sql 2008 yg lain
SET @file_date =
    CONVERT(varchar(8), GETDATE(), 112) + '-' +
    RIGHT('0' + CONVERT(varchar(2), DATEPART(HOUR, GETDATE())), 2) +
    RIGHT('0' + CONVERT(varchar(2), DATEPART(MINUTE, GETDATE())), 2) +
    RIGHT('0' + CONVERT(varchar(2), DATEPART(SECOND, GETDATE())), 2); ***/
    ------------------------------------------------------------
    -- Folder backup
    ------------------------------------------------------------
    SET @backup_folder =
        'C:\backup\manual\' + @folder_date;


    ------------------------------------------------------------
    -- Filename database utama
    ------------------------------------------------------------
    SET @backup_file1 =
        @backup_folder + '\' +
        @dbname + '_' +
        @file_date + '.bak';

    ------------------------------------------------------------
    -- Filename database DMART
    ------------------------------------------------------------
    SET @backup_file2 =
        @backup_folder + '\' +
        @dbname_dmart + '_' +
        @file_date + '.bak';

    ------------------------------------------------------------
    -- Informasi awal
    ------------------------------------------------------------
    RAISERROR(
        '========================================',
        10,
        1
    ) WITH NOWAIT;

    RAISERROR(
        '       MANUAL DATABASE BACKUP',
        10,
        1
    ) WITH NOWAIT;

    RAISERROR(
        '========================================',
        10,
        1
    ) WITH NOWAIT;

    RAISERROR(
        'Database utama : %s',
        10,
        1,
        @dbname
    ) WITH NOWAIT;

    RAISERROR(
        'Database DMART : %s',
        10,
        1,
        @dbname_dmart
    ) WITH NOWAIT;

    ------------------------------------------------------------
    -- Informasi pilihan
    ------------------------------------------------------------
    IF @backup_option = 1
    BEGIN
        RAISERROR(
            'Pilihan       : Backup database utama saja',
            10,
            1
        ) WITH NOWAIT;
    END
    ELSE
    BEGIN
        RAISERROR(
            'Pilihan       : Backup database utama + DMART',
            10,
            1
        ) WITH NOWAIT;
    END;

    ------------------------------------------------------------
    -- Kalau pilihan 2 tetapi DMART tidak ada
    ------------------------------------------------------------
    IF @backup_option = 2
       AND @dmart_exists = 0
    BEGIN

        RAISERROR(
            'INFO: Database %s tidak ditemukan.',
            10,
            1,
            @dbname_dmart
        ) WITH NOWAIT;

        RAISERROR(
            'INFO: Backup DMART akan di-SKIP.',
            10,
            1
        ) WITH NOWAIT;

    END;

    ------------------------------------------------------------
    -- Buat folder backup
    ------------------------------------------------------------
    SET @cmd =
        'IF NOT EXIST "' +
        @backup_folder +
        '" MKDIR "' +
        @backup_folder +
        '"';

    EXEC xp_cmdshell @cmd, NO_OUTPUT;

    ------------------------------------------------------------
    -- BACKUP DATABASE UTAMA
    ------------------------------------------------------------
    RAISERROR(
        '----------------------------------------',
        10,
        1
    ) WITH NOWAIT;

    RAISERROR(
        'Mulai backup database %s...',
        10,
        1,
        @dbname
    ) WITH NOWAIT;


    BACKUP DATABASE @dbname
    TO DISK = @backup_file1
    WITH
        COMPRESSION,
        STATS = 10;

    ------------------------------------------------------------
    -- BACKUP DATABASE DMART
    --
    -- Hanya jika:
    --   @backup_option = 2
    --   dan database DMART ada
    ------------------------------------------------------------
    IF @backup_option = 2
       AND @dmart_exists = 1
    BEGIN

        RAISERROR(
            '----------------------------------------',
            10,
            1
        ) WITH NOWAIT;

        RAISERROR(
            'Mulai backup database %s...',
            10,
            1,
            @dbname_dmart
        ) WITH NOWAIT;

        BACKUP DATABASE @dbname_dmart
        TO DISK = @backup_file2
        WITH
            COMPRESSION,
            STATS = 10;

    END;

    ------------------------------------------------------------
    -- HASIL AKHIR
    ------------------------------------------------------------
    RAISERROR(
        '========================================',
        10,
        1
    ) WITH NOWAIT;

    RAISERROR(
        'BACKUP SELESAI',
        10,
        1
    ) WITH NOWAIT;

    RAISERROR(
        '========================================',
        10,
        1
    ) WITH NOWAIT;

    SELECT
        @dbname AS [Database Utama],

        CASE
            WHEN @backup_option = 1
                THEN 'Backup database utama saja'

            WHEN @backup_option = 2
                 AND @dmart_exists = 1
                THEN 'Backup database utama + DMART'

            WHEN @backup_option = 2
                 AND @dmart_exists = 0
                THEN 'Backup database utama + DMART SKIP (database tidak ada)'
        END AS [Pilihan],

        @backup_file1 AS [Backup File Utama],

        CASE
            WHEN @backup_option = 2
                 AND @dmart_exists = 1
                THEN @backup_file2
            ELSE NULL
        END AS [Backup File DMART];

END;
GO


5. log command prompt 

log  

========================================
       MANUAL DATABASE BACKUP
========================================
Database utama : dbatools
Database DMART : dbatools_dmart
Pilihan       : Backup database utama + DMART
INFO: Database dbatools_dmart tidak ditemukan.
INFO: Backup DMART akan di-SKIP.
----------------------------------------
Mulai backup database dbatools...
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 688 pages for database 'dbatools', file 'dbatools' on file 1.
Processed 2 pages for database 'dbatools', file 'dbatools_log' on file 1.
BACKUP DATABASE successfully processed 690 pages in 0.086 seconds (62.636 MB/sec).
========================================
BACKUP SELESAI
========================================
========================================
       MANUAL DATABASE BACKUP
========================================
Database utama : teguhth
Database DMART : teguhth_dmart
Pilihan       : Backup database utama + DMART
----------------------------------------
Mulai backup database teguhth...
14 percent processed.
29 percent processed.
44 percent processed.
59 percent processed.
67 percent processed.
82 percent processed.
90 percent processed.
100 percent processed.
Processed 864 pages for database 'teguhth', file 'teguhth' on file 1.
Processed 2 pages for database 'teguhth', file 'teguhth_log' on file 1.
BACKUP DATABASE successfully processed 866 pages in 0.060 seconds (112.695 MB/sec).
----------------------------------------
Mulai backup database teguhth_dmart...
11 percent processed.
20 percent processed.
31 percent processed.
40 percent processed.
51 percent processed.
60 percent processed.
70 percent processed.
81 percent processed.
90 percent processed.
100 percent processed.
Processed 448 pages for database 'teguhth_dmart', file 'teguhth_dmart' on file 1.
Processed 2 pages for database 'teguhth_dmart', file 'teguhth_dmart_log' on file 1.
BACKUP DATABASE successfully processed 450 pages in 0.081 seconds (43.354 MB/sec).
========================================
BACKUP SELESAI
========================================
========================================
       MANUAL DATABASE BACKUP
========================================
Database utama : teguhth
Database DMART : teguhth_dmart
Pilihan       : Backup database utama + DMART
----------------------------------------
Mulai backup database teguhth...
15 percent processed.
30 percent processed.
45 percent processed.
60 percent processed.
82 percent processed.
90 percent processed.
100 percent processed.
Processed 864 pages for database 'teguhth', file 'teguhth' on file 1.
Processed 2 pages for database 'teguhth', file 'teguhth_log' on file 1.
BACKUP DATABASE successfully processed 866 pages in 0.074 seconds (91.374 MB/sec).
----------------------------------------
Mulai backup database teguhth_dmart...
11 percent processed.
20 percent processed.
31 percent processed.
40 percent processed.
51 percent processed.
60 percent processed.
71 percent processed.
81 percent processed.
90 percent processed.
100 percent processed.
Processed 448 pages for database 'teguhth_dmart', file 'teguhth_dmart' on file 1.
Processed 2 pages for database 'teguhth_dmart', file 'teguhth_dmart_log' on file 1.
BACKUP DATABASE successfully processed 450 pages in 0.080 seconds (43.896 MB/sec).
========================================
BACKUP SELESAI
========================================
========================================
       MANUAL DATABASE BACKUP
========================================
Database utama : teguhth
Database DMART : teguhth_dmart
Pilihan       : Backup database utama saja
----------------------------------------
Mulai backup database teguhth...
15 percent processed.
30 percent processed.
45 percent processed.
60 percent processed.
82 percent processed.
90 percent processed.
100 percent processed.
Processed 864 pages for database 'teguhth', file 'teguhth' on file 1.
Processed 2 pages for database 'teguhth', file 'teguhth_log' on file 1.
BACKUP DATABASE successfully processed 866 pages in 0.074 seconds (91.374 MB/sec).
========================================
BACKUP SELESAI
========================================
========================================
       MANUAL DATABASE BACKUP
========================================
Database utama : teguhth
Database DMART : teguhth_dmart
Pilihan       : Backup database utama + DMART
----------------------------------------
Mulai backup database teguhth...
15 percent processed.
30 percent processed.
45 percent processed.
60 percent processed.
82 percent processed.
90 percent processed.
100 percent processed.
Processed 864 pages for database 'teguhth', file 'teguhth' on file 1.
Processed 2 pages for database 'teguhth', file 'teguhth_log' on file 1.
BACKUP DATABASE successfully processed 866 pages in 0.080 seconds (84.521 MB/sec).
----------------------------------------
Mulai backup database teguhth_dmart...
11 percent processed.
20 percent processed.
31 percent processed.
40 percent processed.
51 percent processed.
60 percent processed.
70 percent processed.
81 percent processed.
90 percent processed.
100 percent processed.
Processed 448 pages for database 'teguhth_dmart', file 'teguhth_dmart' on file 1.
Processed 2 pages for database 'teguhth_dmart', file 'teguhth_dmart_log' on file 1.
BACKUP DATABASE successfully processed 450 pages in 0.050 seconds (70.234 MB/sec).
========================================
BACKUP SELESAI
========================================

Completion time: 2026-08-12T15:34:07.0378670+07:00