.::: Shell Script to compare table Database MariaDB MySQL :::.

 

  correlation with https://teguhth.blogspot.com/2025/03/query-to-check-table-column-compare_28.html

1. check table server1
 

 

.::: Shell Script to compare value table a Database MariaDB MySQL :::.

  

correlation with https://teguhth.blogspot.com/2025/03/query-to-check-table-column-compare_28.html

1. check value table barang server1
 

 

.::: Trial Backup & Restore MariaDB MySQL with crontab -l / crontab -e & /etc/crontab :::.

 

A. Prepare Script backup & Restore

1. create backup script

[root@teguhth cron]# pwd
/data/cron
[root@teguhth cron]# cat backup_test.sh
echo "backup full mulai" > log_backup.txt
date >> log_backup.txt

mysqldump -uroot -pxxx -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --routines=true --triggers=true --no-data=false teguhth | gzip -c > teguhth_ori.sql.gz ;

echo "backup full selesai " >> log_backup.txt
date  >> log_backup.txt
[root@teguhth cron]#

.::: Simulation Query Select from Java to MariaDB using mariadb-java-client-3.5.3.jar (MariaDB Driver) :::.

  

https://teguhth.blogspot.com/2025/06/simulation-connection-java-to-mariadb.html

1. Script java 

[root@teguhth maria]# cat queryjoin.java
import java.sql.*;

public class queryjoin {
    public static void main(String[] args) {
        String url = "jdbc:mariadb://10.10.10.90:3306/teguhth"; // Ganti IP jika perlu
        String user = "admin";
        String pass = "admin";

        try {
            Connection conn = DriverManager.getConnection(url, user, pass);

            // Jalankan query
//            String query = "select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;";

//String query = "SELECT b.NAMA_BARANG, s.NAMA_SUPLIER, p.TANGGAL_PASOK, p.JUMLAH_PASOK, (SELECT SLEEP(20)) as DELAY FROM barang b, suplier s, pasok p WHERE b.KODE_BARANG = p.KODE_BARANG AND s.KODE_SUPLIER = p.KODE_SUPLIER;";

//String query ="select * from pembelian;";
 

.::: Simulation connection java to MariaDB for Success & Fail using mysql-connector-j-8.0.33.jar (MySQL Driver) (com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure. The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.) :::..

 

1. Download (Platform independent) MySQL Driver

https://repo1.maven.org/maven2/com/mysql/mysql-connector-j/8.0.33/mysql-connector-j-8.0.33.jar

java -version
dnf install java-17-openjdk-devel -y    # CentOS 9

2. check login mariadb

[root@teguhth ~]# mysql -h 10.10.10.90 -uadmin -p teguhth
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 11.4.4-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

.::: Simulation connection java to MariaDB for Success & Fail using mariadb-java-client-3.5.3.jar (MariaDB Driver) (java.sql.SQLNonTransientConnectionException: Socket fail to connect to 10.10.10.9. No route to host) :::.

 

1. Download (Platform independent)

https://mariadb.com/downloads/connectors/connectors-data-access/java8-connector
 

java -version
dnf install java-17-openjdk-devel -y    # CentOS 9

2. check login mariadb

[root@teguhth ~]# mysql -h 10.10.10.90 -uadmin -p teguhth
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 11.4.4-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

.::: Move TempDB to other folder (move SQL Server MDF and LDF Files location):::.

 

 
correlation https://teguhth.blogspot.com/2023/06/move-sql-server-mdf-and-ldf-files.html

1. check 

sp_helpdb tempdb

USE tempdb;
GO
SELECT 
    name AS [Logical Name],
    physical_name AS [File Path],
    type_desc AS [File Type],
    size * 8 / 1024 AS [Size (MB)],
    max_size,
    growth,
    is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');

.::: Quary query to simulation High CPU & High Memory Usage In EDB PostgreSQL :::.

 

 
A. Simulation High CPU

1. Contoh query untuk Simulasi CPU Tinggi
 
Untuk mensimulasikan CPU utilization tinggi di PostgreSQL menggunakan query, kamu bisa menjalankan perintah yang bersifat berat secara berulang, misalnya:

PERINGATAN!
Skrip di bawah akan membuat CPU usage PostgreSQL tinggi, gunakan di lingkungan development/test, bukan production.


CREATE OR REPLACE PROCEDURE cpu_hog()
LANGUAGE plpgsql
AS $$
DECLARE
    start_time timestamp := clock_timestamp();
    elapsed interval;
    i integer := 0;
    j double precision := 0;
BEGIN
    LOOP
        j := sqrt(i * random());
        i := i + 1;

        elapsed := clock_timestamp() - start_time;
        EXIT WHEN elapsed >= interval '20 seconds';
    END LOOP;
END;
$$;
 

.::: Shell Script Kasir cashier with & Without MariaDB :::.

 

A. Simple Cashier without Database

1. Struktur File listbarang.txt

2. Script: kasir.sh
 
[root@teguhth program]# cat kasir.sh

#!/bin/bash

file_barang="listbarang.txt"

# Fungsi untuk menampilkan daftar barang
tampilkan_barang() {
    echo -e "\nDaftar Barang:"
    echo "---------------------------"
    while IFS="|" read -r id nama harga
    do
        echo "$id. $nama - Rp$harga"
    done < "$file_barang"
    echo "---------------------------"
}

.::: Quary to simulation High CPU & High Memory Usage In MySQL MariaDB :::.

 

A. Simulation High CPU

1. Contoh untuk Simulasi CPU Tinggi
 
Untuk mensimulasikan CPU utilization tinggi di SQL Server menggunakan T-SQL, kamu bisa menjalankan perintah yang bersifat berat secara berulang, misalnya:

PERINGATAN!
Skrip di bawah akan membuat CPU usage SQL Server tinggi, gunakan di lingkungan development/test, bukan production.

USE teguhth;
DELIMITER $$

DROP PROCEDURE IF EXISTS CPU_HOG$$

CREATE PROCEDURE CPU_HOG()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE j DOUBLE DEFAULT 0;

    WHILE i < 10000000 DO
        SET j = SQRT(i * RAND());
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

.::: Quary T-SQL to simulation High CPU & High Memory Usage In SQL Server :::.

 

A. Simulation High CPU

1. Contoh T-SQL untuk Simulasi CPU Tinggi
 
Untuk mensimulasikan CPU utilization tinggi di SQL Server menggunakan T-SQL, kamu bisa menjalankan perintah yang bersifat berat secara berulang, misalnya:

PERINGATAN!
Skrip di bawah akan membuat CPU usage SQL Server tinggi, gunakan di lingkungan development/test, bukan production.

USE tempdb;
GO

IF OBJECT_ID('dbo.CPU_HOG') IS NOT NULL
    DROP PROCEDURE dbo.CPU_HOG;
GO

CREATE PROCEDURE dbo.CPU_HOG
AS
BEGIN
    DECLARE @i INT = 0;
    DECLARE @j FLOAT = 0;

    WHILE (@i < 10000000)
    BEGIN
        SET @j = SQRT(@i * RAND()); -- operasi matematika berat
        SET @i = @i + 1;
    END
END;
GO
 

.::: Insert data table to another table with different Database in Oracle Database :::.

 

A. Without Date

1. Check

select * from pembelian;
 

2. table after create
CREATE TABLE pembelian_clone (
    KODE_PEMBELIAN     CHAR(10) NOT NULL,
    KODE_BARANG        CHAR(6) NOT NULL,
    KODE_CUSTOMER      CHAR(6) NOT NULL,
    TANGGAL_PEMBELIAN  DATE DEFAULT NULL,
    JUMLAH_PEMBELIAN   DECIMAL(4,0) DEFAULT NULL
);
 

.::: Insert data table to another table with different Database in PostgreSQL EDB :::.

 

A. Without Date

1. Check

select * from pembelian;
 

2. table after create
CREATE TABLE pembelian_clone (
    KODE_PEMBELIAN     CHAR(10) NOT NULL,
    KODE_BARANG        CHAR(6) NOT NULL,
    KODE_CUSTOMER      CHAR(6) NOT NULL,
    TANGGAL_PEMBELIAN  DATE DEFAULT NULL,
    JUMLAH_PEMBELIAN   DECIMAL(4,0) DEFAULT NULL
);
 

.::: Insert data table to another table with different Database in MariaDB :::.

 

A. Without Date

1. Check

select * from teguhth.pembelian;
 
2. table after create
CREATE TABLE pembelian_clone (
    KODE_PEMBELIAN CHAR(10) NOT NULL,
    KODE_BARANG CHAR(6) NOT NULL,
    KODE_CUSTOMER CHAR(6) NOT NULL,
    TANGGAL_PEMBELIAN DATE DEFAULT NULL,
    JUMLAH_PEMBELIAN DECIMAL(4,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 

.::: Setting up and using the mutt email client in Linux for alert NMS or Other :::.

 

Berikut contoh penggunaan mutt untuk mengirim email dari command line di CentOS 9:

A. Setup

1. update repository

sudo dnf update -y

2. setup mutt

sudo dnf install mutt -y

3. check

mutt -v
 

.::: how to use DBMS_CRYPTO.ENCRYPT dan DBMS_CRYPTO.DECRYPT in Oracle as example table pembelian :::.

 

Oke! Saya akan kasih kamu contoh penggunaan DBMS_CRYPTO.ENCRYPT dan DBMS_CRYPTO.DECRYPT pada tabel pembelian. Di sini, kita akan menyimpan kolom credit_card_number dalam bentuk terenkripsi, dan mendekripsinya saat dibaca.

Studi Kasus: Tabel pembelian_card

1. grant access

GRANT EXECUTE ON DBMS_CRYPTO TO admin;
commit;

2. Buat tabel:

CREATE TABLE pembelian_card (
    id               NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nama_pembeli     VARCHAR2(100),
    credit_card_enc  RAW(2000) -- data terenkripsi
);

.::: Backup restore Export import expdp impdp from schema to another schema in oracle using crontab :::.

 

correlation https://teguhth.blogspot.com/2024/12/backup-restore-export-import-expdb.html


1. backup manual

[oracle@teguhth ~]$ expdp aisyah/hanin@localhost:1521/tgh DIRECTORY=DATA_PUMP_DIR DUMPFILE=aisyah_schema.dmp LOGFILE=aisyah_schema.log SCHEMAS=aisyah

Export: Release 19.0.0.0.0 - Production on Tue Apr 22 14:10:38 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "AISYAH"."SYS_EXPORT_SCHEMA_01":  aisyah/********@localhost:1521/tgh DIRECTORY=DATA_PUMP_DIR DUMPFILE=aisyah_schema.dmp LOGFILE=aisyah_schema.log SCHEMAS=aisyah
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "AISYAH"."BARANG"                           6.984 KB       6 rows
. . exported "AISYAH"."CUSTOMER"                         7.554 KB       6 rows
. . exported "AISYAH"."EMPLOYEES"                        6.359 KB       2 rows
. . exported "AISYAH"."EMPLOYEES_CHANGE_LOG"             8.312 KB       5 rows
. . exported "AISYAH"."PASOK"                            7.695 KB      12 rows
. . exported "AISYAH"."PEMBELIAN"                        7.835 KB      15 rows
. . exported "AISYAH"."SUPLIER"                          7.578 KB       6 rows
Master table "AISYAH"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for AISYAH.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/backup/aisyah_schema.dmp
Job "AISYAH"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Apr 22 14:11:39 2025 elapsed 0 00:00:58

[oracle@teguhth ~]$
 

.::: Enable Change Data Capture (CDC) Oracle Database using TRIGGER with session_user & client_ip :::.

 

Berikut adalah langkah-langkah untuk mengaktifkan Change Data Capture (CDC) di Oracle menggunakan Trigger, termasuk contoh tabel, isi tabel, dan implementasi trigger untuk melacak perubahan data:

1. Membuat Tabel Sumber
Tabel ini adalah tabel utama tempat data disimpan, dan perubahan pada tabel ini akan dilacak.

CREATE TABLE EMPLOYEES (
    EMP_ID NUMBER PRIMARY KEY,
    EMP_NAME VARCHAR2(50),
    DEPARTMENT_ID NUMBER,
    SALARY NUMBER
);
 

.::: How to enable CDC (Change Data Capture) in SQL Server Using Trigger with client_ip :::.

 

Berikut adalah implementasi Change Data Capture (CDC) menggunakan trigger di SQL Server, termasuk contoh tabel, data awal, serta operasi INSERT, UPDATE, dan DELETE.

correlation  with http://teguhth.blogspot.com/2022/11/how-to-enable-cdc-change-data-capture.html
https://teguhth.blogspot.com/2024/12/how-to-enable-cdc-change-data-capture.html
 
1. Membuat Tabel Utama
Misalkan tabel utama bernama Products:

CREATE TABLE Products (
    ProductID INT IDENTITY PRIMARY KEY,
    ProductName NVARCHAR(100),
    Price DECIMAL(10, 2),
    Stock INT
);

.::: Query Check user session, current, Client IP in MariaDB MySQL, PostgreSQL EDB, MSSQL SQL Server, Oracle :::.

 

1. MariaDB MySQL

select USER();
 

2. PostgreSQL

SELECT session_user, CURRENT_USER || '@' || host(inet_client_addr()) AS user_with_ip;  
SELECT CURRENT_USER || '@' || host(inet_client_addr()) AS user_with_ip;
SELECT COALESCE(host(inet_client_addr()), 'localhost') AS ip_only;
SELECT session_user,CURRENT_USER || '@' || inet_client_addr() AS user_with_ip;

.::: How to enable CDC (Change Data Capture) in PostgreSQL, EDB with user,ip client :::.

 

correlation https://teguhth.blogspot.com/2024/04/how-to-enable-cdc-change-data-capture.html

1. Enable WAL (Write-Ahead Logging): PostgreSQL uses Write-Ahead Logging for durability. Ensure that your PostgreSQL server is configured to use WAL.

2. Create Audit Table: Create an audit table where you will log the changes.

CREATE TABLE audit_table (
    id SERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    action_type TEXT NOT NULL,
    old_data JSONB,
    new_data JSONB,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    performed_by VARCHAR(100),
    ip_client TEXT NOT NULL
);

.::: How to enable CDC (Change Data Capture) in MariaDB, MySQL, MarinaDB in Table with user() information :::.

 

correlation https://teguhth.blogspot.com/2024/03/how-to-enable-cdc-change-data-capture.html


Enable Binary Logging
1. check table for sample

SELECT *,@@hostname,@@version FROM teguhth.pembelian
 
2. Create table for cdc

# create table

CREATE TABLE audit_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(100),
    action_type ENUM('INSERT', 'UPDATE', 'DELETE'),
    old_data JSON,
    new_data JSON,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    performed_by VARCHAR(100)
);

ALTER TABLE audit_table
ADD COLUMN performed_by VARCHAR(100);

SELECT *,@@hostname,@@version FROM audit_table;

.::: Insert data table to another table with different Database in SQL Server :::.

 

  A. Without Date

1. Check

select * from teguhth..pembelian
 

2. create table & insert firstime

-- pertama kali insert table
select * into dwh..pembelian_clone from teguhth..pembelian
 

.::: Shrink Disk VMWare for Linux & Windows Virtual Machine :::.

 A. Shrink Disk Linux Virtual Machine

1. check before shrink
 

2. Shrink via Terminal

dd if=/dev/zero of=zerofile bs=1M
 

.::: Setup Polybase in SQL Server for External Table MSSQL :::.

 

Fungsi PolyBase di SQL Server untuk Sumber Eksternal
PolyBase di SQL Server memungkinkan Anda untuk mengakses data dari berbagai sumber eksternal, termasuk SQL Server lain dan MSSQL. Dengan PolyBase, Anda dapat menjalankan query T-SQL di SQL Server yang menggabungkan data dari sumber eksternal tersebut tanpa memindahkan data.

1. setup ODBC MSSQL

2. Buat Credential untuk Login

CREATE DATABASE SCOPED CREDENTIAL RemoteCred
WITH IDENTITY = 'admin', SECRET = 'admin';

.::: Setup Polybase in SQL Server for External Table PostgreSQL :::.

 

Fungsi PolyBase di SQL Server untuk Sumber Eksternal
PolyBase di SQL Server memungkinkan Anda untuk mengakses data dari berbagai sumber eksternal, termasuk SQL Server lain dan PostgreSQL. Dengan PolyBase, Anda dapat menjalankan query T-SQL di SQL Server yang menggabungkan data dari sumber eksternal tersebut tanpa memindahkan data.

1. setup ODBC PostgreSQL
 

2. Buat Credential untuk Login

CREATE DATABASE SCOPED CREDENTIAL PGCred
WITH IDENTITY = 'admin', SECRET = 'admin'

.::: Setup Polybase in SQL Server for External Table MariaDB :::.

 

Fungsi PolyBase di SQL Server untuk Sumber Eksternal
PolyBase di SQL Server memungkinkan Anda untuk mengakses data dari berbagai sumber eksternal, termasuk SQL Server lain dan MariaDB. Dengan PolyBase, Anda dapat menjalankan query T-SQL di SQL Server yang menggabungkan data dari sumber eksternal tersebut tanpa memindahkan data.

1. setup ODBC MariaDB
 

2. Buat Credential untuk Login

CREATE DATABASE SCOPED CREDENTIAL MariadbCred
WITH IDENTITY = 'admin', SECRET = 'admin';

.::: Query to check table, column, compare schema database, structure table in Oracle Database :::.

 

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. ALL_TABLES & ALL_TAB_COLUMNS

SELECT OWNER, TABLE_NAME
FROM ALL_TABLES
WHERE OWNER = 'AISYAH';

SELECT OWNER, TABLE_NAME
FROM ALL_TABLES
WHERE OWNER = 'TEGUHTH';
 

.::: Query to check table, column, compare schema database, structure table in EDB PostgreSQL :::.

 

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

## from teguhth akses teguhth
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = 'teguhth' AND TABLE_SCHEMA = 'public';
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'public';

## from teguhth akses aisyahdb
SELECT * FROM dblink('dbname=aisyahdb host=10.10.10.9 user=admin password=xxx',
    'SELECT table_catalog, table_schema, table_name, table_type
     FROM information_schema.tables WHERE table_schema = ''public''')
AS t(table_catalog text, table_schema text, table_name text, table_type text);
 

.::: Query to check table, column, compare schema database, structure table in MariaDB MySQL :::.

 

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 INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'teguhth';
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'aisyahdb';

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'teguhth';
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'aisyahdb';
 

 

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

 

correlation https://teguhth.blogspot.com/2025/02/script-expdp-backup-daily-database.html

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)

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

 

correlation https://teguhth.blogspot.com/2025/02/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
);
 

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

 

correlation https://teguhth.blogspot.com/2025/02/script-backup-daily-multiple-database.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;
 

.::: How to configure Transparent Data Encryption (TDE), Monitoring, Remove TDE, remove certificate, test backup & Restore in SQL Server :::.

 

correlation with https://teguhth.blogspot.com/2024/06/how-to-configure-transparent-data.html

A. Create TDE

1. Create Master Key

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='TDEpassword!';
GO
 
2. Create Certificate protected by master key
CREATE CERTIFICATE TDE_Cert_source
WITH
SUBJECT='Database_Encryption Source';
GO
 

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

 

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

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