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

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