[MSSQL] Transact-SQL, DB 파일 경로 이동(DB detach, attach)

version: Microsoft SQL Server 2012 (SP3)

 

! 사용 중인 프로시저가 있을 경우 detach가 진행 되지 않는다.
! 운영 서비스에 영향이 있는지 확인해보고 오프라인 전환 후 진행.

 

! If the procedure is running or using DB, you can't detach the Database. 
! First, you have to check the Service in operation. After that, switching the database offline.

use master;
GO
EXEC sp_detach_db @dbname = N'mydb';
GO

 

! detach 후, mdf 및 ldf 파일 복사 진행.
! After detach the databse, you can copy the mdf and ldf file.

! 복사가 완료 된 후, Attach 진행.
! if the file copy is finished normally, you can attach the DB with new path.

USE master;
GO
CREATE DATABASE PlatformService
    ON (FILENAME = 'H:\SQL_DATA3\mydb.mdf'),
    (FILENAME = 'H:\SQL_DATA3\mydb_log.ldf')
    FOR ATTACH;
GO

 

! attach 후 정상 작동 여부 확인. DB를 오프라인으로 전환했을 경우, 온라인 전환.

! When the tasks are completed successfully, Check that the DB is working properly. 
! If you switched the database offline, you have to switching the db status to online.