{"id":303,"date":"2016-02-03T17:09:17","date_gmt":"2016-02-03T08:09:17","guid":{"rendered":"http:\/\/www.onepage.co.kr\/wordpress\/?p=303"},"modified":"2016-04-01T00:43:23","modified_gmt":"2016-03-31T15:43:23","slug":"ms-sql-server-database-restore","status":"publish","type":"post","link":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/2016\/02\/03\/ms-sql-server-database-restore\/","title":{"rendered":"ms sql server database restore"},"content":{"rendered":"

\u200b<\/a>\ub370\uc774\ud0c0\ubca0\uc774\uc2a4 \ubcf5\uc6d0.<\/p>\n

<\/div>\n
http:\/\/m.blog.naver.com\/mavis5\/10079514586<\/div>\n
https:\/\/msdn.microsoft.com\/ko-kr\/library\/ms189272(v=sql.120).aspx<\/div>\n
— \ubcf5\uad6c \uc900\ube44<\/div>\n
USE MSDB<\/div>\n
GO\ud6a8<\/div>\n
— \ubc31\uc5c5\ud30c\uc77c \ud655\uc778 (TYPE:D – \ud480, L – \ub85c\uadf8, I – \ucc28\ub4f1\ubc31\uc5c5)<\/div>\n
SELECT\u00a0BACKUP_START_DATE, TYPE, DATABASE_NAME, PHYSICAL_DEVICE_NAME<\/div>\n
FROM\u00a0MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B ON A.MEDIA_SET_ID = B.MEDIA_SET_ID<\/div>\n
WHERE\u00a0DATABASE_NAME = ‘ELOC2001’<\/div>\n
AND\u00a0\u00a0BACKUP_START_DATE > GETDATE() – 2<\/div>\n
ORDER BY BACKUP_START_DATE DESC<\/div>\n
<\/div>\n
<\/div>\n
— \ud480\ubc31\uc5c5 \ud30c\uc77c \uc815\ubcf4 \ud655\uc778<\/div>\n
RESTORE HEADERONLY FROM DISK = @FILEPATH –‘L:\\SQL_BACKUP\\AAA.FULL<\/div>\n
RESTORE FILELISTONLY FROM DISK = @FILEPATH –‘L:\\SQL_BACKUP\\AAA.FULL<\/div>\n
<\/div>\n
— \ubcf5\uad6c \uc9c4\ud589<\/div>\n
USE MASTER<\/div>\n
GO<\/div>\n
<\/div>\n
— \ubcf5\uad6c\ubaa8\ub378\uc774 SIMPLE\uc778\uacbd\uc6b0<\/div>\n
\u00a0— 1. \ud480\ubc31\uc5c5 \ubcf5\uad6c \ubc31\uc5c5\uc9c4\ud589<\/div>\n
\u00a0RESTORE DATABASE @DB_NAME<\/div>\n
\u00a0\u00a0FROM DISK = @FILEPATH WITH\u00a0 NORECOVERY<\/div>\n
\u00a0\u00a0–, REPLACE — \ubcf5\uad6c\uc2dc \uae30\uc874 \ud30c\uc77c \uad50\uccb4\ud560 \ub54c<\/div>\n
\u00a0\u00a0–, MOVE ‘@LOGICAL_NAME’ TO ‘@PYSICAL_PATH’ — \ubcf5\uad6c\uc2dc \ud30c\uc77c \uc704\uce58 \ubcc0\uacbd\ud560 \ub54c<\/div>\n
<\/div>\n
\u00a0— 2. \ucc28\ub4f1\ubc31\uc5c5 \ubcf5\uad6c (\ud574\ub2f9\ud558\ub294\uacbd\uc6b0 \ub9cc)<\/div>\n
\u00a0RESTORE DATABASE @DB_NAME<\/div>\n
\u00a0\u00a0FROM DISK= @FILEPATH WITH NORECOVERY<\/div>\n
<\/div>\n
\u00a0— 3. \ubcf5\uad6c \ub9c8\ubb34\ub9ac<\/div>\n
\u00a0RESTORE DATABASE @DB_NAME WITH RECOVERY<\/div>\n
<\/div>\n
\u00a0— 4. \ub2e4\uc911 \uc0ac\uc6a9\uc790\ub85c \ubcc0\uacbd<\/div>\n
\u00a0ALTER DATABASE @DB_NAME SET MULTI_USER<\/div>\n
<\/div>\n
— \ubcf5\uad6c\ubaa8\ub378\uc774 FULL, BULK_LOGGED\uc778\uacbd\uc6b0 (\ucc28\ub4f1\ubc31\uc5c5, \ub85c\uadf8\ubc31\uc5c5 \uc0ac\uc6a9\ud55c\ub2e4)<\/div>\n
\u00a0— 0. \ube44\uc0c1\ub85c\uadf8 \ubc31\uc5c5 : \ub9c8\uc9c0\ub9c9 \ub85c\uadf8 \ubc31\uc5c5\ub85c \uc0ac\uc6a9<\/div>\n
\u00a0BACKUP LOG @DB_NAME TO DISK = @FILEPATH<\/div>\n
\u00a0\u00a0WITH NORECOVERY — db\uc0c1\ud0dc\ub97c \ubcf5\uad6c\uc911\uc73c\ub85c \ubcc0\uacbd\ud55c\ub2e4 -> DB \ubcc0\uacbd\uc0ac\ud56d \uc5c6\uac8c \ud55c\ub2e4!<\/div>\n
\u00a0\u00a0–, { CONTINUE_AFTER_ERROR | NO_TRUNCATE }<\/div>\n
<\/div>\n
\u00a0— 1. \ud480\ubc31\uc5c5 \ubcf5\uad6c<\/div>\n
\u00a0RESTORE DATABASE @DB_NAME\u00a0\u00a0FROM DISK = @FILEPATH WITH\u00a0 NORECOVERY<\/div>\n
\u00a0\u00a0–, MOVE ‘@LOGICAL_NAME’ TO ‘@PYSICAL_PATH’ — \ubcf5\uad6c\uc2dc \ud30c\uc77c \uc704\uce58 \ubcc0\uacbd\ud560 \ub54c<\/div>\n
<\/div>\n
\u00a0— 2. \ucc28\ub4f1\ubc31\uc5c5 \ubcf5\uad6c (\ud574\ub2f9\ud558\ub294\uacbd\uc6b0 \ub9cc)<\/div>\n
\u00a0RESTORE DATABASE @DB_NAME\u00a0\u00a0FROM DISK= @FILEPATH WITH NORECOVERY<\/div>\n
<\/div>\n
\u00a0— 3. \ub85c\uadf8\ubc31\uc5c5 \ubcf5\uad6c<\/div>\n
\u00a0RESTORE LOG @DB_NAME\u00a0\u00a0FROM DISK=@LOG_BAL_FILE_1_PATH WITH NORECOVERY<\/div>\n
\u00a0RESTORE LOG @DB_NAME\u00a0\u00a0FROM DISK=@LOG_BAL_FILE_2_PATH WITH NORECOVERY<\/div>\n
\u00a0RESTORE LOG @DB_NAME\u00a0\u00a0FROM DISK=@LOG_BAL_FILE_3_PATH WITH NORECOVERY<\/div>\n
\u00a0RESTORE LOG @DB_NAME\u00a0\u00a0FROM DISK=@LOG_BAL_FILE_4_PATH WITH NORECOVERY–, STOPAT = ‘0000-00-00 00:00:00.000’ — \uc9c0\uc815\uc2dc\uac04 \ubcf5\uad6c<\/div>\n
<\/div>\n
\u00a0— 4. \ubcf5\uad6c \ub9c8\ubb34\ub9ac<\/div>\n
\u00a0RESTORE DATABASE @DB_NAME WITH RECOVERY<\/div>\n
<\/div>\n
\u00a0— 5. \ub2e4\uc911 \uc0ac\uc6a9\uc790\ub85c \ubcc0\uacbd<\/div>\n
\u00a0ALTER DATABASE @DB_NAME SET MULTI_USER<\/div>\n","protected":false},"excerpt":{"rendered":"

\u200b\ub370\uc774\ud0c0\ubca0\uc774\uc2a4 \ubcf5\uc6d0. http:\/\/m.blog.naver.com\/mavis5\/10079514586 https:\/\/msdn.microsoft.com\/ko-kr\/library\/ms189272(v=sql.120).aspx — \ubcf5\uad6c \uc900\ube44 USE MSDB GO\ud6a8 — \ubc31\uc5c5\ud30c\uc77c \ud655\uc778 (TYPE:D – \ud480, L – \ub85c\uadf8, I – \ucc28\ub4f1\ubc31\uc5c5) SELECT\u00a0BACKUP_START_DATE, TYPE, DATABASE_NAME, PHYSICAL_DEVICE_NAME FROM\u00a0MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B ON A.MEDIA_SET_ID = B.MEDIA_SET_ID WHERE\u00a0DATABASE_NAME = ‘ELOC2001’ AND\u00a0\u00a0BACKUP_START_DATE > GETDATE() – 2 ORDER BY BACKUP_START_DATE DESC — \ud480\ubc31\uc5c5 \ud30c\uc77c \uc815\ubcf4 \ud655\uc778 RESTORE HEADERONLY FROM […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-303","post","type-post","status-publish","format-standard","hentry","category-mssql"],"_links":{"self":[{"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/303","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/comments?post=303"}],"version-history":[{"count":1,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/303\/revisions"}],"predecessor-version":[{"id":304,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/303\/revisions\/304"}],"wp:attachment":[{"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/media?parent=303"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/categories?post=303"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/tags?post=303"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}