.::: Query to check table, column, compare schema database, structure table in SQL Server :::.

 

Untuk membandingkan struktur tabel antara dua database teguhth dan aisyah di SQL Server, kita bisa menggunakan query yang membandingkan schema (skema tabel), kolom, tipe data, dan properti lainnya.

A. Query to check table & system table

1. INFORMATION_SCHEMA
select * from teguhth.INFORMATION_SCHEMA.tables
select * from aisyah.INFORMATION_SCHEMA.tables

select * from teguhth.INFORMATION_SCHEMA.COLUMNS
select * from aisyah.INFORMATION_SCHEMA.COLUMNS
 

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

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