[MSSQL]데이터베이스 이동

ALTER DATABASE 재배치 프로시저를 사용하여 사용자 데이터베이스를 이동하는 방법을 정리하였습니다.

일반적으로 동일한 SQL Server 인스턴스 내에서 파일 이동이 필요할 경우에 이 방법을 사용하며, 만약 다른 인스턴스나  다른 SQL Server로 이동한다면 분리 및 연결(sp_attach_db, sp_detach_db 시스템 저장 프로시져) 방법 또는 백업 및 복원 방법을 이용하면 됩니다.


[시나리오]
1. 데이터베이스명 : LAIGO

2. 변경 전 파일 경로
   1) C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\LAIGO.mdf
   2) C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\LAIGO_log.ldf

3. 변경 후 파일 경로
   1) D:\Data\laigo.mdf  
   2) D:\Data\laigo_log.ldf


[작업절차]

USE MASTER
GO

-- 1. 파일의 논리 이름을 확인합니다.
SELECT file_id, name, physical_name  FROM sys.master_files WHERE database_id = db_id('LAIGO');
/* LAIGO, LAIGO_LOG */

-- 2. 30초 후 모든 작업을 롤백하고 단일 사용자 모드로 전환합니다.
ALTER DATABASE LAIGO SET SINGLE_USER WITH ROLLBACK AFTER 30

** 에러 발생 시 세션 KILL 후에 3번 스탭 진행
select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame   from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid where d.name = 'MonitoringDB'
go 
kill 51 --spid
go

-- 3. LAIGO 데이터베이스 오프라인 상태로 전환합니다.
ALTER DATABASE LAIGO SET OFFLINE

ALTER DATABASE MonitoringDB SET OFFLINE

-- 4. mdf,ldf 파일 위치를 변경합니다.


-- 5. mdf 파일 위치 정보를 변경합니다.
ALTER DATABASE LAIGO MODIFY FILE (NAME=LAIGO, FILENAME='d:\data\laigo.mdf')

ALTER DATABASE MonitoringDB MODIFY FILE (NAME=MonitoringDB, FILENAME='d:\SQLDATA\MonitoringDB.mdf')

-- 6. ldf 파일 위치 정보를 변경합니다.
ALTER DATABASE LAIGO MODIFY FILE (NAME=LAIGO_LOG, FILENAME='d:\data\laigo_log.ldf')

ALTER DATABASE MonitoringDB MODIFY FILE (NAME=MonitoringDB_log, FILENAME='d:\SQLDATA\MonitoringDB_log.ldf')

-- 7. 데이버테이스를 온라인 상태로 전환합니다.
ALTER DATABASE LAIGO SET ONLINE

ALTER DATABASE MonitoringDB SET ONLINE 

--8. 멀티 사용자 모드로 변경합니다.
ALTER DATABASE LAIGO SET MULTI_USER

ALTER DATABASE MonitoringDB SET MULTI_USER

-- 9. 정상적으로 변경되었는지 확인합니다.
SELECT  name, physical_name  AS CurrentLocation, state_desc  FROM  sys.master_files
WHERE  database_id  = DB_ID(N'LAIGO');

[참고자료]
사용자 데이터베이스 이동
http://msdn.microsoft.com/ko-kr/library/ms345483.aspx

방법: 분리 및 연결을 사용하여 데이터베이스 이동(Transact-SQL)
http://msdn.microsoft.com/ko-kr/library/ms187858.aspx

시스템 데이터베이스 이동
http://technet.microsoft.com/ko-kr/library/ms345408.aspx