MySQL/Oracle/MS-SQL 리눅스용 복원 스크립트

#################################################
#
# Oracle DB 복원
#
#################################################
sqlplus "system/manager as sysdba"
  -- 스키마 및 테이블스페이스 샥제
  -- Oracle 12c의 이전버전 호환을 위한 설정
  ALTER SESSION SET "_ORACLE_SCRIPT"=TRUE;
  DROP USER u_ssc_1820 CASCADE;
  DROP TABLESPACE ts_ssc_1820 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
  -- 스키마 및 테이블스페이스 다시 생성
  ALTER SESSION SET "_ORACLE_SCRIPT"=TRUE;
  CREATE TABLESPACE ts_ssc_1820 DATAFILE '/opt/oracle/oradata/ORCLCDB/ts_ssc_1820.dbf' SIZE 2M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
  CREATE USER u_ssc_1820 IDENTIFIED BY "<PASSWORD>";
  ALTER USER u_ssc_1820 DEFAULT TABLESPACE ts_ssc_1820;
  ALTER USER u_ssc_1820 TEMPORARY TABLESPACE TEMP;
  GRANT CONNECT, DBA, RESOURCE TO u_ssc_1820;
# 복원
sqlplus "system/manager as sysdba"
  CREATE DIRECTORY DIR_SSC_BACKUP AS '/work/db_backup/oracle';
impdp 'u_ssc_1820/<PASSWORD>@ORCLCDB' SCHEMAS=u_ssc_1820 DIRECTORY=DIR_SSC_BACKUP DUMPFILE=20230509_131353_db_ssc_1820.dmp LOGFILE=20230509_131353_db_ssc_1820.log
sqlplus "system/manager as sysdba"
  -- 필요한 경우 디렉토리 삭제
  DROP DIRECTORY DIR_SSC_BACKUP;

#################################################
#
# Microsoft SQL DB 복원
#
#################################################
# DB 삭제
sqlcmd -S TCP:10.10.10.105 -U SA -P'<PASSWORD>' -Q "DROP DATABASE [db_ssc_2020]"
# 복원
sqlcmd -S TCP:10.10.10.105 -U SA -P'<PASSWORD>' -Q "RESTORE DATABASE [db_ssc_2020] FROM DISK = N'/work/db_backup/mssql/20230509_131353_db_ssc_2020.bak' WITH FILE = 1, NOUNLOAD, REPLACE, NORECOVERY, STATS = 5"
sqlcmd -S TCP:10.10.10.105 -U SA -P'<PASSWORD>' -Q "RESTORE DATABASE [db_ssc_2020] WITH RECOVERY"

#################################################
#
# MySQL 복원
#
#################################################
mysql -u root -p<PASSWORD>
  -- DB와 사용자를 삭제한다.
  DROP DATABASE IF EXISTS db_ssc_2222;
  DROP USER IF EXISTS 'u_ssc'@'localhost';
  DROP USER IF EXISTS 'u_ssc'@'%';
  -- DB와 사용자를 생성한다.
  CREATE DATABASE db_ssc_2222 CHARACTER SET utf8mb3 COLLATE utf8mb3_bin;
  CREATE USER 'u_ssc'@'localhost' IDENTIFIED BY '<PASSWORD>';
  CREATE USER 'u_ssc'@'%' IDENTIFIED BY '<PASSWORD>';
  GRANT ALL PRIVILEGES ON db_ssc_2222.* TO 'u_ssc'@'localhost';
  GRANT ALL PRIVILEGES ON db_ssc_2222.* TO 'u_ssc'@'%';
# 복원
mysql -u root -p<PASSWORD> db_ssc_2222 < /work/db_backup\mysql\db_ssc_2222.sql