[22기 이승주] MSSQL DB Attach&Detach

<TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; BORDER-COLLAPSE: collapse; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoTableGrid border=1 cellSpacing=0 cellPadding=0> <TBODY> <TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"> <TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 253.5pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt" rowSpan=2 width=338> MSSQL DB Attach&Detach</TD> <TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77.95pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=top width=104> 작성자</TD> <TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 129.75pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=top width=173> 이 승 주</TD></TR> <TR style="mso-yfti-irow: 1; mso-yfti-lastrow: yes"> <TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77.95pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=104> 작성일</TD> <TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 129.75pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=173> 11-12-15</TD></TR></TBODY></TABLE>   <TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; BORDER-COLLAPSE: collapse; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoTableGrid border=1 cellSpacing=0 cellPadding=0> <TBODY> <TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"> <TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 461.2pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt" vAlign=top width=615> DB가 저장된 MDF파일을 다른 서버로 옮겨야 할 경우… MSSQL server2008의 SQL Server Management Studio를 실행시키고 새 쿼리를 눌러 select  * from master..sysaltfiles 쿼리로 MDF파일경로와 정보를 찾을 수 있다.</TD></TR> <TR style="mso-yfti-irow: 1; mso-yfti-lastrow: yes"> <TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 461.2pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=615>   <TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; BORDER-COLLAPSE: collapse; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoTableGrid border=1 cellSpacing=0 cellPadding=0> <TBODY> <TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"> <TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 449.65pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt" vAlign=top width=600> <v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v:path><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype>   <img src="https://t1.daumcdn.net/cfile/cafe/1221BF424EE954D92C" class="txc-image" width="312" height="370" style="WIDTH: 312px; FLOAT: none; HEIGHT: 370px; CLEAR: none" actualwidth="418" border="0" hspace="1" vspace="1" id="A_1221BF424EE954D92C0DCF"/>   모든 프로그램에서 Microsoft Server 2008의 SQL Server Management Studio 실행     <img src="https://t1.daumcdn.net/cfile/cafe/123E04444EE9551803" class="txc-image" style="FLOAT: none; CLEAR: none" actualwidth="404" border="0" hspace="1" vspace="1" width="404" id="A_123E04444EE9551803D445"/>   select  * from master..sysaltfiles 실행   <o:p style="TEXT-ALIGN: center"><img src="https://t1.daumcdn.net/cfile/cafe/1135FA404EE9552F2D" class="txc-image" style="FLOAT: none; CLEAR: none" actualwidth="1024" border="0" hspace="1" vspace="1" width="1024" id="A_1135FA404EE9552F2D7C67"/></TD></TR></TBODY></TABLE>     <TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; BORDER-COLLAPSE: collapse; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoTableGrid border=1 cellSpacing=0 cellPadding=0> <TBODY> <TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"> <TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 449.65pt; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt" vAlign=top width=600> MDF 파일복사 & Detach하기 사용중인 MDF파일은 복사할 수 없다. <o:p style="TEXT-ALIGN: center"><img src="https://t1.daumcdn.net/cfile/cafe/18444B404EE955521F" class="txc-image" style="FLOAT: none; CLEAR: none" actualwidth="655" border="0" hspace="1" vspace="1" width="655" id="A_18444B404EE955521F7B7F"/>   쿼리에 alter database [해당DB] set single_user를 입력하고 실행하여 단일 사용자 엑세스 모드로 바꾸고 sp_detach_db 를 실행시켜 DB를 분리시킨 후 MDF파일을 복사할 수 있게 해준다.   <o:p style="TEXT-ALIGN: center"><img src="https://t1.daumcdn.net/cfile/cafe/125D453E4EE955732B" class="txc-image" style="FLOAT: none; CLEAR: none" actualwidth="589" border="0" hspace="1" vspace="1" width="589" id="A_125D453E4EE955732B23A7"/>    <o:p style="TEXT-ALIGN: center"><img src="https://t1.daumcdn.net/cfile/cafe/152B1B424EE9558521" class="txc-image" style="FLOAT: none; CLEAR: none" actualwidth="785" border="0" hspace="1" vspace="1" width="785" id="A_152B1B424EE9558521CA85"/>     <img src="https://t1.daumcdn.net/cfile/cafe/1323B8434EE9559B31" class="txc-image" style="FLOAT: none; CLEAR: none" actualwidth="632" border="0" hspace="1" vspace="1" width="632" id="A_1323B8434EE9559B31EB42"/>   DVDstore 데이터베이스가 사라진 것을 볼 수 있다.(물리적으로는 존재함)</TD></TR></TBODY></TABLE>   <TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; WIDTH: 453.35pt; BORDER-COLLAPSE: collapse; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoTableGrid border=1 cellSpacing=0 cellPadding=0 width=604> <TBODY> <TR style="HEIGHT: 392.7pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"> <TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 453.35pt; PADDING-RIGHT: 5.4pt; HEIGHT: 392.7pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt" vAlign=top width=604> 분리된 데이터베이스 Attach하기 <TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; BORDER-COLLAPSE: collapse; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoTableGrid border=1 cellSpacing=0 cellPadding=0> <TBODY> <TR style="HEIGHT: 33.3pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"> <TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 192.6pt; PADDING-RIGHT: 5.4pt; HEIGHT: 33.3pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0cm; mso-border-alt: solid windowtext .5pt" vAlign=top width=257> exec sp_attach_db 'DVDstore', 'C:\TESTDB\DVDstore.mdf', 'C:\TESTDB\DVDstore_log.ldf'</TD></TR></TBODY></TABLE>   위와 같이 쿼리를 실행시키면 데이터베이스에 해당 DB가 Attach된다.   <o:p style="TEXT-ALIGN: center"><img src="https://t1.daumcdn.net/cfile/cafe/202AC7434EE955B72A" class="txc-image" style="FLOAT: none; CLEAR: none" actualwidth="784" border="0" hspace="1" vspace="1" width="784" id="A_202AC7434EE955B72A2F2E"/>   <o:p style="TEXT-ALIGN: center"><img src="https://t1.daumcdn.net/cfile/cafe/2007B13E4EE955C50F" class="txc-image" style="FLOAT: none; CLEAR: none" alt="더블클릭을 하시면 이미지를 수정할 수 있습니다" actualwidth="625" border="0" hspace="1" vspace="1" width="625" id="A_2007B13E4EE955C50F1623"/></TD></TR></TBODY></TABLE> </TD></TR></TBODY></TABLE>   참조  - <A href="http://cafe.daum.net/smbitpro/QucC/54">http://cafe.daum.net/smbitpro/QucC/54</A> - 데이터를 사랑하는 모임(ilovedata) <A class=tx-link href="http://cafe.naver.com/ilovedata.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=300&" target=_blank>http://cafe.naver.com/ilovedata.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=300&</A>