[2k] 손상된 시스템 데이터베이스 복구

/******************************************************************************************** -- Title : [2k] 손상된 시스템 데이터베이스 복구 -- Reference : blog.naver.com/hdae134 -- Key word : 시스템 데이터베이스 복구 system database recovery ********************************************************************************************/

시스템 데이터베이스가 손상된 경우 SQL Server 서비스가 시작하지 못하거나 일부 기능이 정상적으로 실행되지 않을 수 있습니다. 아래의 표는 각 시스템 데이터베이스가 손상되었을 경우 발생하는 문제점을 설명합니다. 손상 데이터베이스 손상 시 문제점 복구를 위한 작업 master SQL Server 서비스 시작 실패 Rebuildm.exe 수행 후 백업을 사용한 복구 model tempdb를 비롯한 데이터베이스 생성 실패 및 SQL Server 서비스 시작 실패 Rebuildm.exe 수행 후 백업을 사용한 복구 , sp_attach_db msdb SQL Agent 서비스 시작 실패 상동 tempdb tempdb를 사용하는 작업 실패 (데이터 복구, 정렬, 집계 등) 서비스 재시작 등

1. master 데이터베이스 복구 및 이동

master 데이터베이스 복구

master 데이터베이스가 손상되면 SQL Server는 서비스 시작이 실패하며 따라서 복구 작업을 포함한 어떤 작업도 할 수 없습니다. 이렇게 master 데이터베이스가 손상되었다면 일단 SQL Server 서비스가 시작할 수 있도록 Rebuildm.exe 유틸리티를 사용해서 모든 시스템 데이터베이스를 초기화하거나 특정 시점의 master.mdf 파일과 mastlog.ldf 파일의 복사본을 사용하여 서비스를 시작할 수 있습니다. master 데이터베이스를 복구하고자 하는 경우에는 SQL Server 서비스를 반드시 단일 사용자 모드로 시작하여야 하며 현재 설치되어 있는 SQL Server의 빌드 번호와 백업된 master 데이터베이스의 빌드 번호가 일치해야만 합니다. [경고] 시스템 데이터베이스 복구의 경우 현재 설치된 SQL Server의 빌드 번호와 백업된 시스템 데이터베이스의 빌드 번호가 반드시 일치해야만 복구가 가능합니다. 만약, 백업된 master 데이터베이스의 빌드 번호가 현재 설치되어있는 버전의 빌드 번호와 일치하지 않는 경우에는 다음과 같이 오류 메시지가 반환되고 복구 작업이 실패합니다.

SQL Server의 빌드 번호는 서비스 팩이나 보안 패치를 적용할 때 변경됩니다. 따라서, DBA 는 SQL Server에 서비스 팩이나 보안 패치 등을 적용하는 경우에 반드시 시스템 데이터베이스를 백업해 두어야 합니다. [주의] 시스템 데이터베이스의 빌드 번호는 SQL Server 서비스 팩과 보안 패치의 적용에 따라 변경됩니다. 따라서 DBA는 서비스 팩이나 보안 패치를 설치하는 경우 반드시 시스템 데이터베이스를 백업합니다. 뿐만 아니라 설치된 서비스 팩과 보안 패치 파일을 따로 저장하고 설치 정보를 문서화해서 보관합니다.

서비스 팩이나 보안 패치의 빌드 번호를 확인하는 방법

① 서비스 팩이나 보안 패치 설치 파일의 빌드 번호 구성은 아래 그림과 같습니다. <보안 패치의 빌드 번호 구성> 빌드 넘버 : ProductName- KBArticleNumber-X.YY.ZZZZ-LangName.exe 1) ProductName은 제품 이름과 버전 정보입니다. 2) KBArticleNumber는 관련된 Microsoft 기술 자료 문서의 ID입니다. 3) X는 주 버전 번호를 나타냅니다. 4) YY는 두 자리 수의 부 버전 번호를 나타냅니다. 5) ZZZZ 는 보안 패치 등 핫픽스 번호를 나타냅니다. (빌드 번호) 6) LangName 은 보안패치가 현지화된 언어의 세 자리 약어입니다. 예를 들어, KOR(한국어), ENU(영어), JPN (일본) 등이 있습니다. 예) SQL2000-KB815495-8.00.0818-KOR.exe / SQL2000-KB899761-v8.00.2040-x86x64-KOR.exe ② 현재 설치되어있는 SQL Server 빌드 번호 확인은 xp_msver, sp_server_info 등의 저장 프로시저나 @@version, SERVERPROPERTY‘( ProductVersion’) 시스템 함수를 사용하 여 가능합니다. 다음은 @@version 시스템 함수를 사용하여 확인하는 방법입니다.

③ 시스템 데이터베이스 백업의 빌드 번호는 RESTORE HEADERONLY 구문을 통해서 확인 하게 됩니다. 다음은 master 데이터베이스 백업의 빌드 번호를 확인하는 경우입니다.

이제 SQL Server의 빌드 번호와 백업의 빌드 번호를 확인하였다면 일치 여부에 따라서 해당 되는 복구방법을 살펴보도록 하겠습니다.

CASE 1. 현재 설치되어있는 SQL Server 빌드 번호보다 master 데이터베이스 백업의               빌드 번호가 낮은 경우

이와 같은 경우는 먼저 SQL Server를 재 설치하고 master 데이터베이스를 복구합니다. 1) 백업된 시스템 데이터베이스 빌드 번호와 동일한 서비스 팩과 보안 패치를 준비합니다. 2) 복구하고자 하는 인스턴스를 [프로그램 추가/삭제] 애플릿을 통해 삭제합니다. [참고] SQL Server를 [프로그램 추가/삭제] 애플릿을 통해서 삭제할 수 없는 경우에는 http://support.microsoft.com/default.aspx?scid=kb;ko;290991의 문서를 참고하여 수동으로 제거하기 바랍니다. 3) 2)번에서 준비한 SQL Server 2000 및 서비스 팩, 보안 패치를 순서에 따라 설치합니다. 4) SQL Server 서비스를 단일 사용자 모드에서 시작하고 master 데이터베이스를 복구합니다.

CASE 2. 현재 설치되어있는 SQL Server 빌드 번호와 시스템 데이터베이스 백업의               빌드 번호가 일치하는 경우

1) Rebuildm.exe 유틸리티를 실행합니다.     방법① : 시작 버튼을 누른 뒤 실행창에서 Rebuildm.exe를 입력한 뒤 엔터 키를 누릅니다.     방법② : Windows 탐색기를 사용하여“C:\Program Files\Microsoft SQLServer\80\Tools                 \Binn”폴더로 이동하여 Rebuildm.exe를 더블 클릭합니다. [주의] SQL Server 프로그램 CD의 x86\DATA 폴더를 이용해서 Rebuildm.exe 유틸리티를 사용하는 경우 SQL Server 2000 프로그램 CD의 파일은 읽기 전용 속성을 변경하지 못하는 버그로 인하여 설치에 실패하게 됩니다. 따라서, SQL Server 프로그램 CD의 x86\DATA 폴더를 하드 디스크로 복사한 뒤 읽기 전용 속성을 해제하고 사용해야 합니다. [참조 문서] http://support.microsoft.com/default.aspx?scid=kb;en-us;273572 2) [찾아보기] 버튼을 눌러서 초기 시스템 데이터베이스 파일이 들어 있는 폴더의 경로를 지정합니다.

3) 데이터 정렬은 SQL Server 설치시의 정렬과 동일한지 확인합니다. 기본적으로 운영 체제 의 언어에 따라 설정됩니다. 다시 작성 버튼을 눌러 재구성을 진행합니다. 4) 시스템 데이터베이스의 재구성이 완료되면 SQL Server 서비스를 종료합니다. 명령 프롬 프트를 사용해서 SQL Server 서비스를 단일 사용자 모드로 시작합니다. /m 옵션을 추가 하여 SQL Server 서비스를 시작하면 SQL Server 서비스가 단일 사용자 모드로 시작됩 니다.

5) 쿼리 분석기로 연결한 뒤 master 데이터베이스의 백업을 사용하여 복구를 진행합니다.

현재 설치된 SQL Server 의 master 데이터베이스의 위치를 변경하는 경우

현재 master 데이터베이스가 설치된 위치의 하드 디스크의 저장 공간이 부족한 경우 등의 원인으로 master 데이터베이스의 위치를 변경하고자 하는 경우에는 엔터프라이즈 관리자에서 시작 매개변수 정보를 변경하거나 레지스트리의 해당 값을 변경합니다. 먼저, 엔터프라이즈 관리자를 사용하는 경우입니다. 1) SQL Server 엔터프라이즈 관리자를 실행합니다. 2) [서버의 속성]을 클릭합니다. 3) [시작 매개변수] 버튼을 클릭합니다. 4) [기존 매개 변수] 부분의 -d 에 지정되어있는 경로에는 master 데이터베이스의 데이터 파일을 이동할 위치로 수정하고, -l 에 지정되어 있는 경로에는 master 데이터베이스의 로그 파일을 이동할 위치로 수정합니다. 5) -e 에 지정되어 있는 경로를 오류 로그 파일 폴더를 이동하고자 하는 위치로 수정합니다. 6) [확인] 버튼을 누르고 SQL Server 서비스를 종료합니다. 7) master.mdf 파일과 masterlog.ldf 파일을 이동하고자 하는 위치로 복사합니다. 8) SQL Server 서비스를 시작합니다. 9) 정상적으로 동작하는지 확인한 다음에 원본 파일을 삭제합니다.

<SQL Server 시작 매개 변수 입력 창>

레지스트리를 수정하여 master 데이터베이스의 위치를 변경하려는 경우 시작버튼을 누르 고 실행창에서 Regedit.exe 또는 Regedt32.exe를 입력하고 [확인] 버튼을 눌러 레지스트리 편집기를 실행합니다. 1) “HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer”로 다음 과 같이 이동합니다.

2) SQLArg0 (데이터 파일 위치)/ SQLArg1 (오류 로그 파일 위치)/ SQLArg2 (로그 파일 위치) 부분의 [값 데이터]를 이동하고자 하는 경로로 변경합니다. 3) 레지스트리 편집기를 종료합니다. 4) master.mdf 파일과 masterlog.ldf 파일을 이동하고자 하는 위치로 복사합니다. 5) SQL Server 서비스를 시작합니다. 6) SQL Server가 정상적으로 동작하는지를 확인한 뒤 원본 파일을 삭제합니다.

2. model 데이터베이스 복구 및 이동

model 데이터베이스 복구

model 데이터베이스는 사용자 데이터베이스 및 tempdb를 포함한 시스템 데이터베이스를 만들기 위한 템플릿 역할을 수행합니다. 따라서, model 데이터베이스가 손상된 경우 사용자 데이터베이스를 생성할 수 없게 되고 tempdb도 만들어지지 않습니다. 뿐만 아니라 SQL Server 서비스가 정상적인 상태로 시작할 수 없게 됩니다. 만약, model 데이터베이스가 손상을 입은 상태에서 tempdb도 생성되지 않은 경우라면 tempdb를 사용하는 작업인 복구 프로세스를 진행할 수도 없습니다. 다음에서 model 데이터베이스가 손상되어 tempdb도 생성할 수 없는 경우에 model 데이터 베이스가 복구되지 않는 상황을 살펴 보겠습니다. model 데이터베이스가 손상되면 SQL Server 서비스를 정상적으로 시작할 수 없으므로 다음과 같이 명령프롬프트를 실행하고 SQL Server가 설치된 경로로 이동하여 sqlservr.exe를 -c 옵션과 -T3608 추적 플래그를 사용하여 시작합니다. [참고] -T3608 : master 데이터베이스를 제외한 데이터베이스의 인스턴스 복구 프로세스를 실행하지 않고 서비스를 시작하는 추적 플래그 입니다. T3608 추적 플래그를 지정하여 SQL Server 서비스를 시작하게 되면 SQL Server 오류 로그 에서 master 데이터베이스를 제외한 데이터베이스의 인스턴스 복구 프로세스에 대한 로그가 기록되지 않는 것을 확인할 수 있습니다. 이 상태에서의 model 데이터베이스의 복구는 tempdb를 생성하지 못하였으므로 앞에서 설 명한 대로 다음과 같은 오류 메시지를 남기고 실패하게 됩니다. 따라서, model 데이터베이스를 복구하기 위해서는 master 데이터베이스 복구에서 살펴본 바와 같이 Rebuildm.exe 유틸리티를 이용해서 시스템 데이터베이스를 모두 새로 구성한 뒤 기존 시스템 데이터베이스 백업을 통해서 master, model, msdb를 순서대로 복구하거나 model 데이터베이스만 손상된 상태라면 SQL Server 프로그램 CD의 x86\DATA 폴더의 model.mdf 파일과 model.ldf 파일 또는 기존에 파일 백업으로 복사해 놓은 model 데이터베이스 파일들을 SQL Server 시스템 데이터베이스가 위치한 폴더로 복사한 뒤 서비스를 시작 하는 방법 등을 통해서 SQL Server 서비스를 정상적으로 시작한 뒤 진행해야 합니다. 다음은 Rebuildm.exe 유틸리티를 사용하지 않고 간단하게 SQL Server 프로그램 CD의 model 데이터베이스 파일을 복사해서 SQL Server 서비스를 시작하고 model 데이터베이스를 복구하는 예제입니다. 1) 복구 작업을 하기에 앞서서 현재 SQL Server의 빌드 번호와 model 데이터베이스 백업의 빌드 번호를 확인합니다. 2) 빌드 번호가 서로 일치한다면 해당 model 데이터베이스 백업으로 복구가 가능하므로 SQL Server 프로그램 CD에서 model 데이터베이스 파일을 시스템 데이터베이스 폴더로 복사합니다. 3) 이제 SQL Server 서비스를 시작하고 다음과 같이 복구를 진행합니다.

model 데이터베이스의 이동

사용자 데이터베이스의 이동은 데이터 복사 마법사, 백업 및 복구, sp_detach_db/sp_ attach_db 시스템 저장 프로시저 등을 이용해서 가능합니다. 그렇지만 다음에서 시스템 데이터베이스의 경우 이동과 복사 모두 선택할 수 없는 것처럼 시스템 데이터베이스는 데이 터베이스 복사 마법사를 이용할 수 없고 일반적인 상태에서는 sp_detach_db/ sp_ attach_db 시스템 저장 프로시저도 사용할 수 없습니다. <데이터베이스 복사 마법사> <sp_detach_db 시스템 저장 프로시저 사용의 경우> model 데이터베이스와 msdb 데이터베이스의 이동은 SQL Server 서비스를 서비스가 아닌 응용 프로그램으로 -c 옵션과 -T3608 추적 플래그를 함께 지정하여 시작한 뒤 sp_detach_ db/sp_ attach_db 시스템 저장 프로시저를 사용하여 분리하거나 연결해야 합니다. 수행 방법은 다음과 같습니다. 1) 명령프롬프트를 실행해서 SQL Server 프로그램이 설치된 폴더로 이동합니다. sqlservr.exe -c -T3608 을 입력하고 실행합니다. 2) sp_detach_db 시스템 저장 프로시저를 실행하여 model 데이터베이스를 분리합니다.   3) 이동하고자 하는 위치로 model 데이터베이스 파일을 이동한 뒤 sp_attach_db 시스템 저장 프로시저를 실행해서 연결합니다.   4) SQL Server를 응용 프로그램으로 시작한 명령 프롬프트 창에서 Ctrl+C 키를 입력한 다음 Y 키를 눌러 SQL Server를 종료합니다. 다시 SQL Server 서비스를 정상적으로 시작합니다.

3. msdb 복구 및 이동

msdb 복구

msdb에는 SQL 에이전트 서비스와 관련된 메타데이터가 저장됩니다. 따라서, msdb가 손상된 경우 SQL 에이전트 서비스는 시작이 실패할 수 있습니다. 또한, 백업 및 복제 작업, DTS 패키지 등과 관련된 메타 데이터의 저장소이므로 손상 시 기존에 실시한 데이터베이스 백업의 정보와 복제작업, DTS 패키지 등이 손실됩니다. 그러나, msdb가 손상된 경우라 할지라도 SQL Server 서비스는 정상적으로 동작합니다. 따라서, 가용한 msdb의 백업이 존재하는 경우는 SQL Server 서비스의 재 시작 없이 복구할 수 있습니다. 그렇지만 이전에 살펴본 시스템 데이터베이스 복구작업과 마찬가지로 SQL Server의 빌드 번호와 백업의 빌드 번호가 일치해야 복구할 수 있음을 기억하기 바랍니다. 빌드 번호의 확인과 복구 방법은 master 데이터베이스 복구를 참조하기 바랍니다. msdb의 가용한 백업이 없는 경우에는 SQL Server 프로그램 CD에서 msdb 파일들을 복사해서 사용할 수 있습니다. 그러나, 이러한 경우 기존에 msdb에 저장된 작업, 운영자, 경고, DTS 패키지 등은 다시 생성해야만 합니다. 따라서, 재난에 대비하기 위해서는 msdb가 변경될 때마다 master 데이터베이스 및 model 데이터베이스와 함께 msdb도 백업할 뿐만 아니라 SQL Server 에이전트에서 생성한 작업, 운영자, 경고 등은 스크립트로 백업하고, DTS 패키지는 DTS 파일로 백업해 놓는 이중의 안전장치를 고려해야 합니다. SQL Server 에이전트에 생성한 작업, 운영자, 경고를 엔터프라이즈 관리자를 통해서 스크립트로 생성하는 과정은 다음과 같습니다. 1) 경고 스크립트 생성 : 엔터프라이즈 관리자 → 관리 → SQL Server 에이전트 → 경고 → 오른쪽 버튼 클릭 → [모든 작업] → [SQL 스크립트 생성] 2) 운영자 스크립트 생성 : 엔터프라이즈 관리자 → 관리 → SQL Server 에이전트 → 운영자 → 오른쪽 버튼 클릭 → [모든 작업] → [SQL 스크립트 생성] 3) 작업 스크립트 생성 : 엔터프라이즈 관리자 → 관리 → SQL Server 에이전트 → 작업 → 오른쪽 버튼 클릭 → [모든 작업] → [SQL 스크립트 생성]

<엔터프라이즈 관리자를 이용한 작업 스크립트 생성>

이와 같이 엔터프라이즈 관리자를 이용해서 스크립트를 생성하는 방법은 SQL-DMO를 사 용하므로 SQL Server 에이전트의 작업으로 생성하고 스케줄을 이용해서 정기적으로 실행 하는 것은 가능하지 않습니다. 그러나, 작업을 스크립트로 생성하는 쿼리를 마이크로소프트 사이트나 기타 커뮤니티 사이트에서 제공하고 있으므로 소개합니다. <AGENT JOB 스크립트 생성> <http://support.microsoft.com/default.aspx?scid=kb;en-us;321835> --sp_OA params DECLARE @cmd varchar(255) -- Command to run DECLARE @oSQLServer int -- OA return object DECLARE @hr int -- Return code --User params DECLARE @FileName varchar(200) -- File name to script jobs out DECLARE @Server varchar(30) -- Server name to run script on. By default, local server. --SQL DMO Constants DECLARE @ScriptType varchar(50) DECLARE @Script2Type varchar(50) SET @ScriptType = '327'  -- Send output to file, Transact-SQL, script permissions, test for existence, used quoted characters. SET @Script2Type = '3074'  -- Script Jobs, Alerts, and use CodePage 1252. --Set the following properties for your server SET @FileName = 'c:\sqlJobs.sql' SET @Server =  @@SERVERNAME --CREATE The SQLDMO Object EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @oSQLServer OUT --Set Windows Authentication EXEC @hr = sp_OASetProperty @oSQLServer, 'LoginSecure', TRUE --Connect to the Server EXEC @hr = sp_OAMethod @oSQLServer,'Connect',NULL,@server --Script the job out to a text file SET @cmd = 'Jobserver.Jobs.Script(' + @ScriptType + ',"' + @FileName                 + '",' + @Script2Type + ')' EXEC @hr = sp_OAMethod @oSQLServer, @cmd --Close the connection to SQL Server --If object is not disconnected, the processes will be orphaned. EXEC @hr = sp_OAMethod @oSQLServer, 'Disconnect' --Destroy object created. exec sp_OADestroy @oSQLServer [참고] 테이블이나 프로시저 등의 데이터베이스 개체 생성 스크립트를 만드는 작업도 sp_OA 확장 저장 프로시저를 사용한 소스의 예제와 SQL Server 프로그램 경로의 Upgrade 폴더에 있는 scptxfr.exe를 사용하는 간단한 소스 예제 등을 다음의 커뮤니티 사이트에서 참조하여 활용하면 편리합니다. 회원 가입 후 참고하기 바랍니다. http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=202336

msdb 이동

시스템 데이터베이스는 복사 마법사를 이용할 수 없고 일반적인 상태에서 sp_detach_db/sp_attach_db 저장 프로시저도 사용할 수 없다는 것은 model 데이터베이스 이동에서 이미 살펴 보았습니다. msdb의 이동도 모델데이터베이스의 이동 방식과 동일합니다. 따라서, T3608 추적 플래그와 함께 응용 프로그램 모드로 시작하여 분리한 뒤 연결을 하여야 합니다. 주의 사항은 model 데이터베이스와 함께 이동시킬 경우 반드시 model 데이터베이스를 먼저 연결한 다음에 msdb를 연결해야 합니다.

4. tempdb 복구 및 이동

tempdb 복구

tempdb는 SQL Server에서 정렬, GROUP BY를 사용한 집계, 커서 사용, 임시테이블 및 테 이블변수 사용, 일부 JOIN, SORT_IN_TEMPDB 옵션을 사용한 인덱스 생성, 데이터베이스의 복구 작업 등에서 사용됩니다. 따라서, 손상 시에는 이와 같은 작업들을 진행할 수 없게 됩니다. tempdb의 손상 시 tempdb를 사용하는 작업은 다음과 같이 913 오류를 발생하며 정상적으로 실행되지 않습니다. [참고] tempdb를 생성하지 않고 SQL Server를 시작하기 위해서는 명령 프롬프트에서 다음과 같이 시작합니다. C:\Program Files\Microsoft SQL Server\MSSQL\binn\sqlservr.exe -c -T3609 다음과 같이 tempdb가 손상되어 주의 대상 상태인 경우 SQL Server 서비스를 재시작만 하더라도 복구되기도 하지만 그렇지 않은 경우도 발생합니다. <TEMPDB 가 손상된 경우> 이런 경우 다음의 절차를 따라서 tempdb를 재 생성합니다. 1) 명령 프롬프트를 실행하고 SQL Server 프로그램이 설치된 경로로 이동합니다. T3608 추적 플래그를 사용하여 응용 프로그램으로 시작합니다. 이때 T4022 추적 플래그도 함께 사용합니다. T4022 추적 플래그는 서비스가 시작될 때 자동으로 실행하도록 구성한 저장 프로시저를 실행되지 않도록 하는 옵션입니다. 상황에 따라서는 -f (최소 구성 시작) 옵션으로 시작해야 하는 경우도 있습니다. 2) 쿼리 분석기에서 sp_resetstatus 프로시저를 사용하여 주의 대상 모드를 해제합니다. 3) SQL Server를 응용 프로그램으로 시작한 명령 프롬프트 창에서 Ctrl+C 키를 입력한 다음에 Y 키를 눌러 SQL Server를 종료합니다. 다시 SQL Server 서비스를 정상적으로 시작합니다.

tempdb 이동

SQL Server 데이터베이스 중 유일하게 tempdb의 경우에는 ALTER DATABASE ~ MODIFY FILE 구문으로 데이터베이스의 파일의 물리적인 경로를 변경할 수 있습니다. 단, 변경한 다음에 SQL Server 서비스를 다시 시작해야만 변경된 위치에 새로운 tempdb가 생성됩니다. 이 경우 이전에 생성된 tempdb의 파일들은 자동으로 삭제되지 않기 때문에 불필요한 공간을 차지하게 되므로 수동으로 삭제합니다. 1) 기존 파일의 위치를 확인합니다. 2) ALTER DATABASE 구문으로 파일의 물리적 경로를 변경합니다. 3) 메시지 창에 표시된 지시대로 SQL Server 서비스를 다시 시작하고 새로운 위치로 변경되었는지 확인한 다음에 기존의 파일을 삭제합니다.