[SQL Server / MS-SQL] 데이터베이스 분리 및 연결 (Detach / Attach)

 

 

---

 

 

데이터베이스를 운영중에 디스크 공간 부족으로 인해 데이터 파일(Data File) 을 새로운 디스크로 옮겨야 할 경우, 데이터베이스 분리 및 연결을 이용할 수 있다. 데이터베이스 분리 및 연결은 같은 컴퓨터의 다른 SQL Server 인스턴스로 변경하거나 데이터베이스를 이동하는 경우 유용하다.

 

데이터베이스를 분리하면 SQL Server 인스턴스에서 해당 데이터베이스가 제거되지만 데이터 파일 (Data File) 과 트랜잭션 로그 파일 (Transaction Log File) 은 그대로 유지된다.

 

※ 데이터베이스를 분리할 수 없는 경우

 - 데이터베이스 스냅샷 (Database Snapshot) 이 있는 경우 → 먼저 해당 데이터베이스의 스냅샷을 삭제해야 한다.

 - 데이터베이스 미러링 (Database Mirroring) 되고 있는 경우 → 먼저 데이터베이스 미러링을 제거해야한다.

 - 데이터베이스가 시스템 데이터베이스인 경우

 

 

1. 데이터베이스 분리 (Database Detach)

[SSMS]

개체 탐색기 (Object Explorer) > (분리할) 데이터베이스 선택 > 태스크 (Tasks) > 분리 (Detach)

연결 삭제 버튼 선택

 - 연결 중인 데이터베이스는 분리할 수 없으므로 연결 삭제를 통해 강제로 끊어줘야 한다.

 

[T-SQL]

skipchecks 가 true 로 설정된 test 데이터베이스를 분리한다.

EXEC sp_detach_db 'test', 'true';

 

 

데이터베이스를 조회하면 해당 데이터베이스가 정상 제거된 것을 확인할 수 있다.

 

 

 

2. 데이터 파일(Data File, mdf/ndf) 및 로그 파일(Log File, ldf) 위치 이동

 

나의 mdf 파일의 경우,

C:\MSSQL\DATA\test.mdf 에서 D:\MSSQL\DATA\test.mdf 로 옮겨주었다.

 

나의 ldf 파일의 경우,

C:\MSSQL\LOG\test_log.ldf 에서 D:\MSSQL\LOG\test_log.ldf 로 옮겨주었다.

 

 

3. 데이터베이스 연결 (Database Attach)

개체 탐색기 (Object Explorer) > 데이터베이스 (Databases) > 연결 (Attach) 선택

 

추가 버튼을 선택하여 옮긴 위치의 데이터 파일 (Data File, mdf) 을 선택해준다.

 

만약, 로그 파일(Log File, ldf) 이 데이터 파일(Data File, mdf) 과 동일한 위치에 있을 경우

로그 파일(Log File, ldf) 도 자동으로 따라온다.

 

그러나 나와 같이 데이터 파일(Data File, mdf) 과 로그 파일(Log File, ldf) 을 분리해서 저장한 경우,

로그 파일(Log File, ldf) 을 아래 그림의 [....] 버튼을 눌러 경로를 지정해주어야한다.

로그 파일(Log File, ldf) 의 경로도 변경해주었으면 확인 버튼을 눌러 적용해보자.

 

[T-SQL]

CREATE DATABASE test
ON (FILENAME = 'D:\MSSQL\DATA\test.mdf'),
       (FILENAME = 'D:\MSSQL\LOG\test_log.ldf')
FOR ATTACH;

 

 

데이터베이스를 조회해보면 해당 데이터베이스가 정상 연결된 것을 확인할 수 있다!

 

 

※ 운영 체제 오류:5 (액세스 거부) 오류 발생

https://ryean.tistory.com/19

메시지 5120, 수준 16, 상태 101, 줄 8
물리적 파일 "D:\MSSQL\DATA\test.mdf"을(를) 열 수 없습니다. 운영 체제 오류 5: "5(액세스가 거부되었습니다.)"

 

 

 

[참고 문헌]

https://docs.microsoft.com/ko-kr/sql/relational-databases/databases/detach-a-database?view=sql-server-ver15

데이터베이스 분리 - SQL Server

https://docs.microsoft.com/ko-kr/sql/relational-databases/databases/database-detach-and-attach-sql-server?view=sql-server-ver15

데이터베이스 분리 및 연결(SQL Server) - SQL Server