DB/MS SQL

SQL Server 데이터베이스 이전

[건희아빠] 2009. 5. 6. 09:07

SQL Server Datafile이 C:\에 설치되어 있는 경우 아래와 같은 방법으로 Datafile을 이동하면 됩니다.

 

==========================================================================================================================

 

이 문서에 나와 있는 모든 예제는 SQL Server가 D:\Mssql7 디렉터리에 설치되어 있고 모든 데이터베이스와 로그 파일은 기본 디렉터리 D:\Mssql7\Data에 있다고 가정합니다. 예제는 모든 데이터베이스의 데이터 및 로그 파일을 E:\Sqldata로 이동합니다.

전제 조건

  • 현재 위치의 모든 데이터베이스 특히 master 데이터베이스의 현재 백업을 만듭니다.
  • 사용자에게는 시스템 관리자(sa) 권한이 있어야 합니다 .
  • 데이터베이스의 모든 데이터 파일 및 로그 파일의 이름과 현재 위치를 알고 있어야 합니다.

    참고: sp_helpfile 저장 프로시저를 사용하면 데이터베이스에서 사용되는 모든 파일의 이름과 현재 위치를 확인할 수 있습니다. 
    use <database_name>
go
sp_helpfile
go
  • 이동하려는 데이터베이스에 대해 단독 액세스 권한이 있어야 합니다. 이 과정 중에 문제가 발생하여 이동한 데이터베이스를 액세스할 수 없거나 SQL Server를 시작할 수 없으면 SQL Server 오류 로그 및 SQL Server 온라인 설명서에서 발생한 오류의 정보를 확인하십시오.

사용자 데이터베이스 이동

다음 예제는 Mydb.mdf 데이터 파일과 Mydblog.ldf 로그 파일이 있는 mydb 데이터베이스를 이동합니다. 이동하려는 데이터베이스에 그 외의 다른 데이터나 로그 파일이 있으면 sp_attach_db 저장 프로시저에 모든 추가 파일의 이름을 쉼표로 구분하여 명시하십시오. sp_detach_db 프로시저는 데이터베이스에 들어 있는 파일을 나열하지 않으므로 데이터베이스에 포함되는 파일의 수가 문제가 되지 않습니다.

  1. 데이터베이스를 다음과 같이 분리합니다. 
       use master
   go
   sp_detach_db 'mydb'
   go
  2. 다음에는 현재 위치(D:\Mssql7\Data)의 데이터 및 로그 파일을 새 위치(E:\Sqldata)에 복사합니다.
  3. 다음과 같이 새 위치로 이동한 파일에 해당하는 데이터베이스를 다시 연결(Re-attach)합니다. 
      use master
  go
  sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'
  go
    sp_helpfile를 사용하여 파일 위치가 변경되었는지 확인합니다. 
       use mydb
   go
   sp_helpfile
   go
    filename 열 값이 새 위치를 반영해야 합니다.

 

Pubs 및 Northwind 이동

사용자 데이터베이스를 이동시킬 때 동일한 프로시저를 수행하십시오.

 

 

MSDB 데이터베이스 이동(SQL Server 2000)

참고: 이 프로시저를 사용하여 msdb 및 model 데이터베이스를 이동하면 model이 먼저 다시 연결되고 msdb가 그 다음에 다시 연결됩니다. msdb가 먼저 다시 연결되면 분리되어 model이 연결될 때까지 다시 연결되지 않습니다.

SQL Server 2000에서는 시스템 데이터베이스를 sp_detach_db 저장 프로시저로 분리할 수 없습니다. 아래와 같은 메시지가 나타나고 sp_detach_db 'msdb' 실행은 실패합니다.
 
            서버: 메시지 7940, 수준 16, 상태 1, 줄 1
            시스템 데이터베이스 master, model, msdb 및 tempdb를 분리할 수 없습니다.
 
 
SQL Server 2000에서 MSDB 데이터베이스를 이동시키려면 다음과 같이 하십시오.
  1. SQL Server 엔터프라이즈 관리자에서 해당 서버 이름을 마우스 오른쪽 단추로 누르고 속성을 누릅니다.
  2. 일반 탭에서 시작 매개 변수를 누릅니다.
  3. 새 매개 변수로 "-T3608"(인용 부호 제외)을 추가합니다.
3608 추적 플래그를 추가한 후 다음과 같이 하십시오.
  1. SQL Server를 중지했다가 다시 시작합니다.
  2. SQL Server 에이전트는 현재 실행되지 않아야 합니다.
  3. msdb 데이터베이스를 다음과 같이 분리합니다. 
    use master
go
sp_detach_db 'msdb'
go
  4. Msdbdata.mdf 파일과 Msdblog.ldf 파일을 현재 위치(D:\Mssql8\Data)에서 새 위치(E:\Mssql8\Data)로 이동시킵니다.
  5. 엔터프라이즈 관리자의 시작 매개 변수 상자에서 -T3608 추적 플래그를 제거합니다.
  6. SQL Server를 중지했다가 다시 시작합니다.
  7. msdb 데이터베이스를 다음과 같이 다시 연결합니다. 
    use master
go 
sp_attach_db 'msdb','E:\Mssql8\Data\msdbdata.mdf','E:\Mssql8\Data\msdblog.ldf' 
go
    참고 -T3608 추적 플래그를 사용해서 SQL Server를 시작하여 msdb 데이터베이스를 다시 연결하려고 하면 아래와 같은 오류가 발생합니다.

              서버: 메시지 615, 수준 21, 상태 1, 줄 1
              데이터베이스 테이블 ID 3, 이름 'model'을(를) 찾을 수 없습니다.

 

Master 데이터베이스의 이동

  1. SQL Server 엔터프라이즈 관리자에 있는 마스터 데이터 및 로그 파일의 경로를 변경합니다.

    참고: 여기서 오류 로그의 위치도 선택적으로 변경할 수 있습니다.
  2. 엔터프라이즈 관리자에서 SQL Server를 마우스 오른쪽 단추로 누른 다음 속성을 누릅니다.
  3. 시작 매개 변수 단추를 누르면 다음 항목이 나타납니다. 
       -dD:\MSSQL7\data\master.mdf
   -eD:\MSSQL7\log\ErrorLog
   -lD:\MSSQL7\data\mastlog.ldf
    -d는 master 데이터베이스 데이터 파일의 정식 경로입니다.

    -e는 오류 로그 파일의 정식 경로입니다.

    -l는 master 데이터베이스 로그 파일의 정식 경로입니다.
  4. 이러한 값을 다음과 같이 변경합니다.
    1. Master.mdf 및 Mastlog.ldf 파일에 대한 현재 항목을 제거합니다.
    2. 새 위치를 지정하는 새 항목을 추가합니다. 
            -dE:\SQLDATA\master.mdf
      -lE:\SQLDATA\mastlog.ldf
  5. SQL Server를 중지합니다.
  6. Master.mdf 및 Mastlog.ldf 파일을 새 위치(E:\Sqldata)에 복사합니다.
  7. SQL Server를 다시 시작합니다.

 

Model 데이터베이스의 이동

model 데이터베이스를 이동하려면 SQL Server는 master 이외의 데이터베이스를 복구하지 않도록 3608 추적 플래그와 함께 시작되어야 합니다.

참고: 이 때 사용자 데이터베이스를 액세스할 수 없습니다. 이 추적 플래그를 사용하는 중에는 다음 단계 외의 작업은 수행하지 말아야 합니다. 3608 추적 플래그를 SQL Server 시작 매개 변수로서 추가하려면: 3608 추적(Trace) 플래그를 추가한 후 다음 단계를 수행합니다.

  1. SQL Server를 중지했다가 다시 시작합니다.
  2. model 데이터베이스를 다음과 같이 분리합니다. 
       use master
   go
   sp_detach_db 'model'
   go
  3. D:\Mssql7\Data에서 E:\Sqldata로 Model.mdf 및 Modellog.ldf를 이동합니다.
  4. model 데이터베이스를 다음과 같이 다시 연결합니다. 
       use master
   go
   sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
   go
  5. 엔터프라이즈 관리자의 시작 매개 변수 상자에서 -T3608 추적 플래그를 제거합니다.
  6. SQL Server를 중지했다가 다시 시작합니다. sp_helpfile을 사용하여 새 위치에 파일이 제대로 이동되었는지를 확인할 수 있습니다. 
       use model
   go
   sp_helpfile
   go

Tempdb 이동

ALTER DATABASE 문을 사용하여 tempdb 파일을 이동할 수 있습니다.

  1. sp_helpfile을 다음과 같이 사용하여 tempdb 데이터베이스의 논리 파일 이름을 알아냅니다. 
    use tempdb
go
sp_helpfile
go
    각 파일의 논리 이름은 name 열에 나와 있습니다. 이 예제는 기본 파일 이름인 tempdev와 templog를 사용합니다.

  2. ALTER DATABASE 문을 사용하여 논리 파일 이름을 다음과 같이 지정합니다. 
    use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
go
    변경 내용을 확인하는 다음과 같은 메시지가 나타납니다.
    sysaltfiles에서 'tempdev' 파일이 수정되었습니다. SQL Server를 다시 시작한 후 오래된 파일을 삭제하십시오.

    sysaltfiles에서 'templog' 파일이 수정되었습니다. SQL Server를 다시 시작한 후 오래된 파일을 삭제하십시오.
  3. SQL Server를 다시 시작해야만 tempdb에서 sp_helpfile을 사용하여 이러한 변경 내용을 확인할 수 있습니다.
  4. SQL Server를 중지했다가 다시 시작합니다.
 
저작자표시 비영리 변경금지