.::: 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
);

.::: 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)   
);
 

.::: 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 ### ---
 

.::: Deploy MariaDB Audit filter by only Create, Insert, Update, Delete :::.

 

 correlation https://teguhth.blogspot.com/2022/08/enable-server-audit-in-mariadb-mysql.html

A. Prepare Filter
1. Create
grep -iE "create " server_audit.log | grep -ivE "select|SHOW|INSERT"

2. Insert
grep -iE "insert into|insert ignore" server_audit.log | grep -ivE "select|CREATE|SHOW"

3. Update
grep -iE "UPDATE .*SET" server_audit.log | grep -ivE "select|CREATE"

4. Delete
grep -i "delete" server_audit.log | grep -ivE "select|CREATE|SHOW|INSERT" 

.::: Backup Database MariaDB with encripted -aes-256-cbc dan restore mariadb database :::.

 

1. backup enkripsi

mysqldump -uroot -pxxx teguhth | openssl enc -aes-256-cbc -md sha1 -salt -out teguhthenc.sql.enc

mysqldump -uroot -pxxx teguhth | openssl enc -pass pass:abcde -aes-256-cbc -md sha1 -salt -out teguhthenc.sql.enc

mysqldump -uroot -pxxx teguhth | openssl enc -pass pass:abcde -aes-256-cbc -md sha1 -salt -out teguhthenc.sql.enc | gzip -c  > teguhthenc.gz.sql.enc

.::: Script Backup Database MariaDB from Master & Check from MaxScale :::.

 

1. Check maxscale
[root@ha01 test]# ip a | grep -i 10.10
    inet 10.10.10.15/24 brd 10.10.10.255 scope global noprefixroute ens33
    inet 10.10.10.110/32 scope global ens33
[root@ha01 test]#

maxctrl list servers

.::: Restore SQL Script Using SQL CMD in MSSQL SQL Server :::.

 

1. check servername
select @@servername,@@version
 

2. Restore script

sqlcmd -S MSSQL -U ha -P ha1 -d teguhth_sqlcmd -i C:\tmp\teguhth_sqlcmd.sql
 

.::: Add Sensor Microsoft SQL V2, Sensor Factory PRTG to Monitoring CPU Memory SQL Server :::.

 

continue from https://teguhth.blogspot.com/2024/03/script-check-cpu-usage-memory-usage.html

A. Configuration to PRTG
1. create file query
 

2. copy query to directory
C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\sql\mssql

.::: Install & Configuration Virtual IP Using Keepalived :::.

 

1. Install keepalived
yum install keepalived -y

[root@teguhth01 ~]# yum install keepalived -y

[root@teguhth02 ~]# yum install keepalived -y

[root@teguhth01 ~]# systemctl enable keepalived
Created symlink from /etc/systemd/system/multi-user.target.wants/keepalived.service to /usr/lib/systemd/system/keepalived.service.
[root@teguhth01 ~]# systemctl start keepalived
[root@teguhth01 ~]#

.::: Script running command Linux using Remote include Restart Shutdown sample for maintenance MariaDB & MaxScale :::.

 

1. Data IP LAB
IP Maxscale : 10.10.10.15
IP Master : 10.10.10.31
IP Slave : 10.10.10.32

2. enable access root without password

yum install -y sshpass
ssh-keygen -t rsa
sshpass -p root ssh-copy-id -p22 -o 'StrictHostKeyChecking=no' root@10.10.10.15
sshpass -p root ssh-copy-id -p22 -o 'StrictHostKeyChecking=no' root@10.10.10.31
sshpass -p root ssh-copy-id -p22 -o 'StrictHostKeyChecking=no' root@10.10.10.32

3. test

ssh root@10.10.10.15 "hostname"
ssh root@10.10.10.31 "hostname"
ssh root@10.10.10.31 "hostname"

.::: Script Backup Daily Multiple Database, Backup FULL, Backup LOG, Backup Incremental with Record Table with start, finish, duration backup logs in MariaDB MySQL MarinaDB :::.

 correlation +update from https://teguhth.blogspot.com/2024/07/script-backup-full-differential.html
https://teguhth.blogspot.com/2025/01/script-backup-daily-backup-full-backup.html

1. Create database & table for save backup log

create database dbatools;
CREATE TABLE backuplogduration  (
    host VARCHAR(255) NOT NULL,
    backuptime  DATETIME NOT NULL,
    start DATETIME NOT NULL,
    finish DATETIME NOT NULL,
    duration VARCHAR(255) NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
 

.::: Script EXPDP Backup Daily Database Oracle 19C with Record to Table with Duration :::.

 

A. Create Backup with record table
1. Create DATA_PUMP_DIR backup directory

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/home/oracle/backup';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO aisyah;

2. create schema (dbatools)

CREATE USER dbatools IDENTIFIED BY dbatools;
GRANT CONNECT, RESOURCE,oem_monitor TO dbatools;
ALTER USER dbatools QUOTA UNLIMITED ON USERS;

-- Memberikan hak akses dasar kepada user dbatools
GRANT CONNECT, RESOURCE,oem_monitor TO dbatools;

-- Memberikan hak akses untuk melakukan dbatoolsistrasi
GRANT CREATE SESSION TO dbatools;
GRANT UNLIMITED TABLESPACE TO dbatools;
GRANT DBA TO dbatools;

.::: Script Backup Full All Database Using Record to Table include start, finish & duration backup in PostgreSQL EDB :::.

 

correlation https://teguhth.blogspot.com/2024/02/script-backup-full-all-database-in.html
https://teguhth.blogspot.com/2025/01/script-backup-full-all-database-using.html

1. create database & table
CREATE TABLE backuplogduration (
    host VARCHAR(255) NOT NULL,
    backuptime TIMESTAMP NOT NULL,
    start TIMESTAMP NOT NULL,
    finish TIMESTAMP NOT NULL,
    duration VARCHAR(255) NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
);