{"id":5980,"date":"2023-05-09T13:19:52","date_gmt":"2023-05-09T04:19:52","guid":{"rendered":"\/blog\/?p=5980"},"modified":"2023-09-21T09:26:20","modified_gmt":"2023-09-21T00:26:20","slug":"mysql-oracle-ms-sql-%ec%9c%88%eb%8f%84%ec%9a%b0%ec%9a%a9-%eb%b0%b1%ec%97%85-%ec%8a%a4%ed%81%ac%eb%a6%bd%ed%8a%b8","status":"publish","type":"post","link":"https:\/\/hasu0707.duckdns.org\/blog\/?p=5980","title":{"rendered":"MySQL\/Oracle\/MS-SQL \uc708\ub3c4\uc6b0\uc6a9 \ubc31\uc5c5\/\ubcf5\uc6d0 \uc2a4\ud06c\ub9bd\ud2b8"},"content":{"rendered":"\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"bat\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">@ECHO OFF\nSET TIMESTAMP_VAL=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%_%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%\nSET TIMESTAMP_VAL=%TIMESTAMP_VAL: =0%\n\nECHO #################################################\nECHO #\nECHO # Oracle DB \ubc31\uc5c5\nECHO #\nECHO #################################################\nREM sqlplus \"system\/manager as sysdba\"\nREM   SELECT * FROM DBA_DIRECTORIES;\nREM   CREATE DIRECTORY DIR_SSC_BACKUP AS 'C:\\database\\backup\\oracle';\nREM   GRANT READ,WRITE ON DIRECTORY DIR_SSC_BACKUP TO u_ssc_1820;\nMKDIR C:\\database\\backup\\oracle\nexpdp \"u_ssc_1820\/&lt;PASSWORD>@ORCL\" SCHEMAS=u_ssc_1820 DIRECTORY=DIR_SSC_BACKUP DUMPFILE=%TIMESTAMP_VAL%_db_ssc_1820 LOGFILE=%TIMESTAMP_VAL%_db_ssc_1820\ngzip -9 C:\\database\\backup\\oracle\\%TIMESTAMP_VAL%_db_ssc_1820.dmp\ngzip -9 C:\\database\\backup\\oracle\\%TIMESTAMP_VAL%_db_ssc_1820.log\n\nREM #################################################\nREM #\nREM # Oracle DB \ubcf5\uc6d0\nREM #\nREM #################################################\nREM sqlplus \"system\/manager as sysdba\"\nREM   -- \uc2a4\ud0a4\ub9c8 \ubc0f \ud14c\uc774\ube14\uc2a4\ud398\uc774\uc2a4 \uc0e5\uc81c\nREM   -- Oracle 12c\uc758 \uc774\uc804\ubc84\uc804 \ud638\ud658\uc744 \uc704\ud55c \uc124\uc815\nREM   ALTER SESSION SET \"_ORACLE_SCRIPT\"=TRUE;\nREM   DROP USER u_ssc_1820 CASCADE;\nREM   DROP TABLESPACE ts_ssc_1820 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;\nREM   -- \uc2a4\ud0a4\ub9c8 \ubc0f \ud14c\uc774\ube14\uc2a4\ud398\uc774\uc2a4 \ub2e4\uc2dc \uc0dd\uc131\nREM   ALTER SESSION SET \"_ORACLE_SCRIPT\"=TRUE;\nREM   CREATE TABLESPACE ts_ssc_1820 DATAFILE 'C:\\database\\oradata\\orcl\\ts_ssc_1820.dbf' SIZE 2M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;\nREM   CREATE USER u_ssc_1820 IDENTIFIED BY \"&lt;PASSWORD>\";\nREM   ALTER USER u_ssc_1820 DEFAULT TABLESPACE ts_ssc_1820;\nREM   ALTER USER u_ssc_1820 TEMPORARY TABLESPACE TEMP;\nREM   GRANT CONNECT, DBA, RESOURCE TO u_ssc_1820;\nREM # \ubcf5\uc6d0\nREM impdp \"u_ssc_1820\/&lt;PASSWORD>@ORCL\" SCHEMAS=u_ssc_1820 DIRECTORY=DIR_SSC_BACKUP DUMPFILE=db_ssc_1820.dmp LOGFILE=db_ssc_1820.log\nREM sqlplus \"system\/manager as sysdba\"\nREM   -- \ud544\uc694\ud55c \uacbd\uc6b0 \ub514\ub809\ud1a0\ub9ac \uc0ad\uc81c\nREM   DROP DIRECTORY DIR_SSC_BACKUP;\nREM ---------------------------------------------\n\nECHO #################################################\nECHO #\nECHO # Microsoft SQL DB \ubc31\uc5c5\nECHO #\nECHO #################################################\nsqlcmd -S tcp:10.10.10.105 -U SA -P&lt;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\"\ngzip -9 C:\\database\\backup\\mssql\\%TIMESTAMP_VAL%_db_ssc_2020.bak\n\nREM #################################################\nREM #\nREM # Microsoft SQL DB \ubcf5\uc6d0\nREM #\nREM #################################################\nREM # DB \uc0ad\uc81c\nREM sqlcmd -S TCP:10.10.10.105 -U SA -P&lt;PASSWORD> -Q \"DROP DATABASE [db_ssc_2020]\"\nREM # \ubcf5\uc6d0\nREM sqlcmd -S tcp:10.10.10.105 -U SA -P&lt;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\"\nREM sqlcmd -S TCP:10.10.10.105 -U SA -P&lt;PASSWORD> -Q \"RESTORE DATABASE [db_ssc_2020] WITH RECOVERY\"\nREM REM ---------------------------------------------\n\nECHO #################################################\nECHO #\nECHO # MySQL \ubc31\uc5c5\nECHO #\nECHO #################################################\nMKDIR C:\\database\\backup\\mysql\nmysqldump --single-transaction --routines --triggers -u root -p&lt;PASSWORD> db_ssc_2222 > C:\\database\\backup\\mysql\\%TIMESTAMP_VAL%_db_ssc_2222.sql\ngzip -9 C:\\database\\backup\\mysql\\%TIMESTAMP_VAL%_db_ssc_2222.sql\n\nREM #################################################\nREM #\nREM # MySQL \ubcf5\uc6d0\nREM #\nREM #################################################\nREM mysql -u root -p&lt;PASSWORD>\nREM   -- DB\uc640 \uc0ac\uc6a9\uc790\ub97c \uc0ad\uc81c\ud55c\ub2e4.\nREM   DROP DATABASE IF EXISTS db_ssc_2222;\nREM   DROP USER IF EXISTS 'u_ssc'@'localhost';\nREM   DROP USER IF EXISTS 'u_ssc'@'%';\nREM   -- DB\uc640 \uc0ac\uc6a9\uc790\ub97c \uc0dd\uc131\ud55c\ub2e4.\nREM   CREATE DATABASE db_ssc_2222 CHARACTER SET utf8mb3 COLLATE utf8mb3_bin;\nREM   CREATE USER 'u_ssc'@'localhost' IDENTIFIED BY '&lt;PASSWORD>';\nREM   CREATE USER 'u_ssc'@'%' IDENTIFIED BY '&lt;PASSWORD>';\nREM   GRANT ALL PRIVILEGES ON db_ssc_2222.* TO 'u_ssc'@'localhost';\nREM   GRANT ALL PRIVILEGES ON db_ssc_2222.* TO 'u_ssc'@'%';\nREM # \ubcf5\uc6d0\nREM mysql -u root -p&lt;PASSWORD> db_ssc_2222 &lt; C:\\database\\backup\\mysql\\db_ssc_2222.sql<\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[10],"tags":[],"class_list":["post-5980","post","type-post","status-publish","format-standard","hentry","category-computing_database"],"_links":{"self":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/5980","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5980"}],"version-history":[{"count":0,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/5980\/revisions"}],"wp:attachment":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5980"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5980"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5980"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}