손상된 부트페이지 복구하기

SQL Server/SQL Server Tip

손상된 부트페이지 복구하기

SungWookKang 2015. 7. 23. 10:48

손상된 부트페이지 복구하기

Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

데이터베이스 장애 중 큰 이슈는 부트페이지 손상이다. 부트 페이지가 손상된 경우에는 데이터베이스를 온라인 또는 긴급 모드로 전환될 수 없다. 이번 포스트는 손상된 부트페이지를 복구하는 방법에 대해서 알아본다.

부트 페이지를 손상하고 복원하기 위한 테스트 데이터베이스를 생성한다. 생성된 데이터베이스를 분리하여 데이터파일에 사용자가 접근할 수 있도록 한다.

-- Drop old database

USE [master];

IF DATABASEPROPERTYEX (N'Company', N'Version') > 0

BEGIN

ALTER DATABASE [Company] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE [Company];

END

-- Create database and table

CREATE DATABASE [Company] ON PRIMARY (

NAME = N'Company',

FILENAME = N'D:\SQLskills\Company.mdf')

LOG ON (

NAME = N'Company_log',

FILENAME = N'D:\SQLskills\Company_log.ldf');

USE [Company];

CREATE TABLE [Sales] (

[SalesID] INT IDENTITY,

[CustomerID] INT DEFAULT CONVERT (INT, 100000 * RAND ()),

[SalesDate] DATETIME DEFAULT GETDATE (),

[SalesAmount] MONEY DEFAULT CONVERT (MONEY, 100 * RAND ()));

CREATE CLUSTERED INDEX [SalesCI] ON [Sales] ([SalesID]);

-- Populate the table

SET NOCOUNT ON;

INSERT INTO [Sales] DEFAULT VALUES;

GO 5000

-- Create some nonclustered indexes

CREATE NONCLUSTERED INDEX [SalesNCI_CustomerID] ON [Sales] ([CustomerID]);

CREATE NONCLUSTERED INDEX [SalesNCI_SalesDate_SalesAmount] ON [Sales] ([SalesDate]) INCLUDE ([SalesAmount]);

-- Create a good backup

BACKUP DATABASE [Company] TO DISK = N'C:\SQLskills\OldCompany.bck'

WITH INIT;

-- And detach it

USE [master]

EXEC sp_detach_db N'Company';

데이터파일의 분리가 완료 되었으면 16진수 편집기(헥사에디터)를 사용하여 데이터파일을 오픈한다.

헥사에디터 다운로드 : http://mh-nexus.de/en/hxd/

오픈한 데이터파일에서 부트페이지의 오프셋(offset)으로 이동한다. 오프셋은 항상 9페이지에 있기 때문에 8192*9 = 73728 오프셋에 있다.

선택 창이 나타나면 10진수(dec)와 선두부터 시작하는 옵션을 선택한다.

데이터베이스 이름을 포함하여 부팅 페이지의 내용을 볼 수 있다.

데이터베이스 이름에 이르기까지 모든 라인을 선택한 후 마우스 오른쪽 버튼을 클릭하고 채우기를 선택한다.

선택한 영역을 00으로 채운다.

00으로 채우고 나면 아래 그림과 같다.

데이터 변경이 완료 되었으면 저장한 다음 에디터를 종료한다. 이제 데이터파일은 손상된 상태로 설정이 완료 되었다.

데이터베이스를 연결을 시도해본다.

USE [master];

-- Try attaching it again

EXEC sp_attach_db @dbname = N'Company',

@filename1 = N'D:\SQLskills\Company.mdf',

@filename2 = N'D:\SQLskills\Company_log.ldf';

연결이 실패하면서 오류가 반환된다.

Msg 1813, Level 16, State 2, Line 5

Could not open new database 'Company'. CREATE DATABASE is aborted.

Msg 824, Level 24, State 2, Line 5

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:9; actual 0:0). It occurred during a read of page (1:9) in database ID 6 at offset 0x00000000012000 in file 'D:\SQLskills\Company.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

손상된 데이터베이스를 복구하기 위해 손상된 데이터베이스 파일을 복사하고 더미 데이터베이스를 생성한 다음 오프라인으로 설정한다.

CREATE DATABASE [Company] ON PRIMARY (

NAME = N'Company',

FILENAME = N'D:\SQLskills\Company.mdf')

LOG ON (

NAME = N'Company_log',

FILENAME = N'D:\SQLskills\Company_log.ldf');

ALTER DATABASE [Company] SET OFFLINE;

생성된 더미 데이터파일을 삭제하고 원래 손상된 데이터베이스 파일을 복사한다. 그리고 온라인으로 실행 한다.

ALTER DATABASE [Company] SET ONLINE;

하지만 연결이 실패되고 작동하지 않는다.

Msg 5181, Level 16, State 5, Line 33

Could not restart database "Company". Reverting to the previous status.

Msg 5069, Level 16, State 1, Line 33

ALTER DATABASE statement failed.

Msg 824, Level 24, State 2, Line 33

현재 데이터베이스 상태는 서스펙트 상태로 나타난다.

SELECT DATABASEPROPERTYEX (N'Company', N'STATUS');

데이터베이스 복구를 위해 응급모드로 변경을 시도하지만 실패 한다.

ALTER DATABASE [Company] SET EMERGENCY;

ALTER DATABASE [Company] SET SINGLE_USER;

Msg 824, Level 24, State 2, Line 43

손상된 부트페이지는 데이터베이스에 액세스 할 수 없기 때문에 작동하지 않는다. 손상된 부트페이지를 복원하기 위해 16진수 편집기를 사용해서 문제를 해결할 수 있다. 현재 생성되어 있는 데이터베이스를 제거하고 다시 손상된 데이터파일을 복사한다.

ALTER DATABASE [Company] SET OFFLINE;

-- ***** Copy off the corrupt files

DROP DATABASE [Company];

이전의 정상적인 백업 파일을 복원한 다음 헥사에디터에서 파일을 오픈할 수 있도록 오프라인으로 설정한다.

RESTORE DATABASE [Company] FROM

DISK = N'C:\SQLskills\OldCompany.bck'

WITH REPLACE;

ALTER DATABASE [Company] SET OFFLINE;

백업 파일에서 복원된 데이터베이스의 부트페이지로 이동한다.

복원된 사본에는 부팅페이지가 손상되지 않은 것을 볼 수 있다. 부트페이지 오프셋을 복사한다. 이 오프셋은 모든 데이터베이스에 동일하다.(12000 ~ 14000 까지 복사)

손상된 파일의 부팅 오프셋으로 이동하여 복사한 부팅 페이지를 덮어쓴다.

빨간색으로 복사된 것을 덮어쓴 것을 확인 할 수 있다. 저장하고 편집기를 종료한다

현재 복원된 파일을 삭제하고 손상된 파일의 이름을 바꾼다. 그리고 데이터베이스를 온라인 한다.

ALTER DATABASE [Company] SET ONLINE;

복구된 부트페이지와 트랜잭션로그가 일치하지 않기 때문에 오류가 발생한다. 긴급모드로 변경하여 CHECKDB를 실행하여 복구 한다.

ALTER DATABASE [Company] SET EMERGENCY;

ALTER DATABASE [Company] SET SINGLE_USER;

DBCC CHECKDB (N'Company', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;

DBCC CHECKDB 작업이 완료되면 데이터베이스에 액세스가 가능하다.

[참고자료]

http://www.sqlskills.com/blogs/paul/disaster-recovery-101-fixing-a-broken-boot-page/

2015-07-09 / 강성욱 / http://sqlmvp.kr

SQLServer, MSSQLSERVER, MSSQL, 데이터베이스 복구, DB복구,DB손상, DBA

'SQL Server > SQL Server Tip' 카테고리의 다른 글

Sys.dm_os_waiting_tasks를 활용한 실행중인 병렬쿼리 확인 (0)	2015.07.23
DBCC DROPCLEANBUFFERS가 작동하지 않을 때 (0)	2015.07.23
Temp table 객체 생성시 세션간 충돌하지 않는 이유 (0)	2015.07.23
SQL Server 데이터베이스 메일 계정 수정 (0)	2015.07.23
XEvent(확장이벤트)를 활용한 활성 로그 모니터링 하기 (0)	2015.07.23

현재글손상된 부트페이지 복구하기

Data Science Lab