@ECHO OFF SET TIMESTAMP_VAL=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%_%TIME:~0,2%%TIME:~3,2%%TIME:~6,2% SET TIMESTAMP_VAL=%TIMESTAMP_VAL: =0% ECHO ################################################# ECHO # ECHO # Oracle DB 백업 ECHO # ECHO ################################################# REM sqlplus "system/manager as sysdba" REM SELECT * FROM DBA_DIRECTORIES; REM CREATE DIRECTORY DIR_SSC_BACKUP AS 'C:\database\backup\oracle'; REM GRANT READ,WRITE ON DIRECTORY DIR_SSC_BACKUP TO u_ssc_1820; MKDIR C:\database\backup\oracle expdp "u_ssc_1820/<PASSWORD>@ORCL" SCHEMAS=u_ssc_1820 DIRECTORY=DIR_SSC_BACKUP DUMPFILE=%TIMESTAMP_VAL%_db_ssc_1820 LOGFILE=%TIMESTAMP_VAL%_db_ssc_1820 gzip -9 C:\database\backup\oracle\%TIMESTAMP_VAL%_db_ssc_1820.dmp gzip -9 C:\database\backup\oracle\%TIMESTAMP_VAL%_db_ssc_1820.log REM ################################################# REM # REM # Oracle DB 복원 REM # REM ################################################# REM sqlplus "system/manager as sysdba" REM -- 스키마 및 테이블스페이스 샥제 REM -- Oracle 12c의 이전버전 호환을 위한 설정 REM ALTER SESSION SET "_ORACLE_SCRIPT"=TRUE; REM DROP USER u_ssc_1820 CASCADE; REM DROP TABLESPACE ts_ssc_1820 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; REM -- 스키마 및 테이블스페이스 다시 생성 REM ALTER SESSION SET "_ORACLE_SCRIPT"=TRUE; REM CREATE TABLESPACE ts_ssc_1820 DATAFILE 'C:\database\oradata\orcl\ts_ssc_1820.dbf' SIZE 2M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE; REM CREATE USER u_ssc_1820 IDENTIFIED BY "<PASSWORD>"; REM ALTER USER u_ssc_1820 DEFAULT TABLESPACE ts_ssc_1820; REM ALTER USER u_ssc_1820 TEMPORARY TABLESPACE TEMP; REM GRANT CONNECT, DBA, RESOURCE TO u_ssc_1820; REM # 복원 REM impdp "u_ssc_1820/<PASSWORD>@ORCL" SCHEMAS=u_ssc_1820 DIRECTORY=DIR_SSC_BACKUP DUMPFILE=db_ssc_1820.dmp LOGFILE=db_ssc_1820.log REM sqlplus "system/manager as sysdba" REM -- 필요한 경우 디렉토리 삭제 REM DROP DIRECTORY DIR_SSC_BACKUP; REM --------------------------------------------- ECHO ################################################# ECHO # ECHO # Microsoft SQL DB 백업 ECHO # ECHO ################################################# sqlcmd -S tcp:10.10.10.105 -U SA -P<PASSWORD> -Q "BACKUP DATABASE [db_ssc_2020] TO DISK = N'C:\database\backup\mssql\%TIMESTAMP_VAL%_db_ssc_2020.bak' WITH NOFORMAT, NOINIT, NAME = 'db_ssc_2020-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10" gzip -9 C:\database\backup\mssql\%TIMESTAMP_VAL%_db_ssc_2020.bak REM ################################################# REM # REM # Microsoft SQL DB 복원 REM # REM ################################################# REM # DB 삭제 REM sqlcmd -S TCP:10.10.10.105 -U SA -P<PASSWORD> -Q "DROP DATABASE [db_ssc_2020]" REM # 복원 REM sqlcmd -S tcp:10.10.10.105 -U SA -P<PASSWORD> -Q "RESTORE DATABASE [db_ssc_2020] FROM DISK = N'C:\database\backup\mssql\db_ssc_2020.bak' WITH FILE = 1, NOUNLOAD, REPLACE, NORECOVERY, STATS = 5" REM sqlcmd -S TCP:10.10.10.105 -U SA -P<PASSWORD> -Q "RESTORE DATABASE [db_ssc_2020] WITH RECOVERY" REM REM --------------------------------------------- ECHO ################################################# ECHO # ECHO # MySQL 백업 ECHO # ECHO ################################################# MKDIR C:\database\backup\mysql mysqldump --single-transaction --routines --triggers -u root -p<PASSWORD> db_ssc_2222 > C:\database\backup\mysql\%TIMESTAMP_VAL%_db_ssc_2222.sql gzip -9 C:\database\backup\mysql\%TIMESTAMP_VAL%_db_ssc_2222.sql REM ################################################# REM # REM # MySQL 복원 REM # REM ################################################# REM mysql -u root -p<PASSWORD> REM -- DB와 사용자를 삭제한다. REM DROP DATABASE IF EXISTS db_ssc_2222; REM DROP USER IF EXISTS 'u_ssc'@'localhost'; REM DROP USER IF EXISTS 'u_ssc'@'%'; REM -- DB와 사용자를 생성한다. REM CREATE DATABASE db_ssc_2222 CHARACTER SET utf8mb3 COLLATE utf8mb3_bin; REM CREATE USER 'u_ssc'@'localhost' IDENTIFIED BY '<PASSWORD>'; REM CREATE USER 'u_ssc'@'%' IDENTIFIED BY '<PASSWORD>'; REM GRANT ALL PRIVILEGES ON db_ssc_2222.* TO 'u_ssc'@'localhost'; REM GRANT ALL PRIVILEGES ON db_ssc_2222.* TO 'u_ssc'@'%'; REM # 복원 REM mysql -u root -p<PASSWORD> db_ssc_2222 < C:\database\backup\mysql\db_ssc_2222.sql