SQL Server Error 824 & Restore Page Level 에서의 복원하는 방법

[참고문서]의 샘플코드로 데모를 해본 결과 잘 된다.

장애 시 적절할 시나리오를 만들어 놓고 대응하면 좋을 것 같다.

 

 

<요구 및 필요사항>

1. 오류발생 전 전체백업본이 존재해야 된다.

2. 적절한 시점에 log 백업을 받아야 된다.

3. 혹시 모르니 장애시점에 전체백업을 한번 더 받자

 

 

 

 

--#.1 환경 만들기

    USE master

    GO

 

    CREATE DATABASE PageLevelRestores

    GO

 

    USE PageLevelRestores

    GO

 

    -- Create a table where every record fits onto 1 page of 8kb

    CREATE TABLE Test

    (

        Filler CHAR(8000)

    )

    GO

 

    -- Insert 4 records

    INSERT INTO Test VALUES (REPLICATE('A', 8000))

    INSERT INTO Test VALUES (REPLICATE('B', 8000))

    INSERT INTO Test VALUES (REPLICATE('C', 8000))

    INSERT INTO Test VALUES (REPLICATE('D', 8000))

    GO

 

    -- Retrieve the selected records

    SELECT * FROM Test

    GO

 

--#.2 정상적으로 운영시 정기적인 전체백업 및 장애발생 시키기

 

    -- Perform a full database backup 일상적으로 전체백업이 수행되는 것이다.

    BACKUP DATABASE PageLevelRestores TO DISK = N'E:\SQLData9\PageLevelRestores.bak'

    GO

 

    -- 적당한 페이지에 트러블을 만들어보자.

    -- Retrieve the first data page for the specified table (columns PageFID and PagePID)

    DBCC IND(PageLevelRestores, Test, -1)

    GO

 

    use master

    go

 

    -- DBCC WRITEPAGE 는 단일사용자모드에서 동작한다.

    ALTER DATABASE PageLevelRestores SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    GO

 

    

    -- Let's corrupt page 118...

    DBCC WRITEPAGE(PageLevelRestores, 1, 118, 0, 1, 0x41, 1)

    DBCC WRITEPAGE(PageLevelRestores, 1, 118, 1, 1, 0x41, 1)

    DBCC WRITEPAGE(PageLevelRestores, 1, 118, 2, 1, 0x41, 1)

    GO

 

    ALTER DATABASE PageLevelRestores SET MULTI_USER

    GO

 

--#.3 장애 인지 시점

    USE PageLevelRestores

    GO

    -- Retrieve the selected records

    SELECT * FROM Test

    GO

    /*

    메시지 824, 수준 24, 상태 2, 줄 54

    SQL Server에서 일관성 기반의 논리적인 I/O 오류가 검색되었습니다:

    체크섬이 잘못되었습니다(예상: 0x82766d91, 실제: 0x22566db1)..

    파일 'E:\Program Files\Microsoft SQL Server\MSSQL12.GHOST\MSSQL\DATA\PageLevelRestores.mdf'의

    오프셋 0x000000000ec000에서 데이터베이스 ID 25에 있는 페이지 (1:118)의 읽기 중 이 오류가 발생했습니다.

    자세한 내용은 SQL Server 오류 로그 또는 시스템 이벤트 로그의 추가 메시지에서 확인할 수 있습니다.

    이는 데이터베이스 무결성을 위협하는 심각한 오류 상태이며 즉시 수정해야 합니다.

    전체 데이터베이스 일관성 검사(DBCC CHECKDB)를 완료하십시오.

    이 오류는 다양한 요인으로 인해 발생할 수 있습니다.

    자세한 내용은 SQL Server 온라인 설명서를 참조하십시오.

    */

 

--#. 4 장애조치 시작하기

    -- corrupted page 확인하기

    select * from msdb.dbo.suspect_pages

    /*

    database_id    file_id    page_id    event_type    error_count    last_update_date

    25            1        118        2            1            2015-12-16 09:34:06.543

    */

 

    SELECT * FROM Test where Filler like 'A%'

 

    sp_readerrorlog

    /*

    오류: 824, 심각도: 24, 상태: 2.

    SQL Server detected a logical consistency-based I/O error: 체크섬이 잘못되었습니다(예상: 0x82766d91, 실제: 0x22566db1).. It occurred during a 읽기 of page (1:118) in database ID 25 at offset 0x000000000ec000 in file 'E:\Program Files\Microsoft SQL Server\MSSQL12.GHOST\MSSQL\DATA\PageLevelRestores.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.

    */

 

    -- Now we have additional transaction that we don't want to loose...

    -- 장애 이후에도 데이터는 입력이 되는 상황을 만들기 위해 작업이다.

    -- 이런 데이터도 모두 살려야 할 임무를 띄고 있다. ㅎㅎㅎ

    INSERT INTO Test VALUES (REPLICATE('E', 8000))

    GO

 

 

[Action Plan]

 

    -- 복구중 또 다른 장애가 발생할 수 있으니 장애난 상태의 전체백업을 해보자.

    -- LSN 문제도 있을 수 있으니, COPY_ONLY 옵션을 사용하면 더 좋을 것 같다.

    BACKUP DATABASE PageLevelRestores TO DISK = N'E:\SQLData9\PageLevelRestores_Last.bak'

    GO

 

    -- Backup the transaction log

    -- 현재 시점까지의 트랜잭션로그를 백업받자.

    BACKUP LOG PageLevelRestores TO

        DISK = 'E:\SQLData9\PageLevelRestores_LOG1.bak'

        WITH INIT

    GO

    /*

    파일 1에서 데이터베이스 'PageLevelRestores', 파일 'PageLevelRestores_log'에 대해 22개의 페이지를 처리했습니다 .

    BACKUP LOG이(가) 22개의 페이지를 0.059초 동안 처리했습니다(2.846MB/초).

    */

 

    -- 일단 돌려보자

    dbcc checkdb('PageLevelRestores')

    /*

    메시지 8939, 수준 16, 상태 98, 줄 104

    테이블 오류: 개체 ID 245575913, 인덱스 ID 0, 파티션 ID 72057594040549376, 할당 단위 ID 72057594045792256(In-row data 유형), 페이지 (1:118). 테스트(IS_OFF (BUF_IOERR, pBUF->bstat))에 실패했습니다. 값은 133129 및 -4입니다.

    메시지 8928, 수준 16, 상태 1, 줄 104

    개체 ID 245575913, 인덱스 ID 0, 파티션 ID 72057594040549376, 할당 단위 ID 72057594045792256(In-row data 유형): 페이지 (1:118)을(를) 처리할 수 없습니다. 자세한 내용은 다른 오류를 참조하십시오.

    */

 

    use master

    go

    -- Restore full database backup

    -- 장애전 전체백업본으로 해당 페이지만 복원하는 방법이다.

    -- 여기서 NORECOVERY 모드다. 그러나, 전체 데이터베이스가 복원중 상태가 아님을 기억하자. 왜 ?? 모름.

    RESTORE DATABASE PageLevelRestores

        PAGE = '1:118'

        FROM DISK = 'E:\SQLData9\PageLevelRestores.bak'

        WITH NORECOVERY, FILE=2

    GO

 

 

    /*

    파일 1에서 데이터베이스 'PageLevelRestores', 파일 'PageLevelRestores'에 대해 1개의 페이지를 처리했습니다 .

    RESTORE DATABASE ... FILE=<name>이(가) 1개의 페이지를 0.126초 동안 처리했습니다(0.062MB/초).

    */

 

 

    -- Backup the tail of the log...

    -- 또 다른 트랜잭션이 발생 할 수 있으니 백업하나 ?? 모름.

    BACKUP LOG PageLevelRestores TO

        DISK = 'E:\SQLData9\PageLevelRestores_LOG_TAIL.bak'

        WITH INIT

    GO

    /*

    파일 1에서 데이터베이스 'PageLevelRestores', 파일 'PageLevelRestores_log'에 대해 6개의 페이지를 처리했습니다 .

    BACKUP LOG이(가) 6개의 페이지를 0.055초 동안 처리했습니다(0.781MB/초).

    */

    select * from PageLevelRestores.dbo.test

 

    /*

    메시지 824, 수준 24, 상태 2, 줄 132

    SQL Server에서 일관성 기반의 논리적인 I/O 오류가 검색되었습니다: 체크섬이 잘못되었습니다

    (예상: 0x82766d91, 실제: 0x22566db1).. 파일 'E:\Program Files\Microsoft SQL Server\MSSQL12.GHOST\MSSQL\DATA\PageLevelRestores.mdf

    '의 오프셋 0x000000000ec000에서 데이터베이스 ID 25에 있는 페이지 (1:118)의 읽기 중

    이 오류가 발생했습니다. 자세한 내용은 SQL Server 오류 로그 또는 시스템

    이벤트 로그의 추가 메시지에서 확인할 수 있습니다. 이는 데이터베이스 무결성을 위협하는

    심각한 오류 상태이며 즉시 수정해야 합니다. 전체 데이터베이스 일관성 검사(DBCC CHECKDB)를 완료하십시오

    . 이 오류는 다양한 요인으로 인해 발생할 수 있습니다. 자세한 내용은 SQL Server 온라인 설명서를 참조하십시오.

 

    -- 최종 전체백업을 받을 경우 현 단계에서는 아래와 같이 오류가 표시됨.

    메시지 829, 수준 21, 상태 1, 줄 163

    데이터베이스 ID 25, 페이지 (1:118)이(가) RestorePending으로 표시되어 있으며 이는 디스크 손상을 나타냅니다. 이 상태로부터 복구하려면 복원을 수행하십시오.

    */

    -- Restore all available log backups in the correct order

    -- 전체백업을 복원을 했으므로 해당 페이지에 대해서만 복원을 하나 ?? 더 모르겠음.

    RESTORE LOG PageLevelRestores FROM

        DISK = 'E:\SQLData9\PageLevelRestores_LOG1.bak'

        WITH NORECOVERY

    GO

    /*

    파일 1에서 데이터베이스 'PageLevelRestores', 파일 'PageLevelRestores'에 대해 0개의 페이지를 처리했습니다 .

    RESTORE LOG이(가) 0개의 페이지를 0.001초 동안 처리했습니다(0.000MB/초).

    */

 

    -- Finally restore the tail log backup

    RESTORE LOG PageLevelRestores FROM

        DISK = 'E:\SQLData9\PageLevelRestores_LOG_TAIL.bak'

        WITH NORECOVERY

    GO

 

    /*

    파일 1에서 데이터베이스 'PageLevelRestores', 파일 'PageLevelRestores'에 대해 0개의 페이지를 처리했습니다 .

    온라인 복원이 완료되었으나, WITH NORECOVERY가 지정되었습니다. RESTORE WITH RECOVERY를 사용하여 영향을 받은 데이터를 온라인 상태로 만드십시오.

    RESTORE LOG이(가) 0개의 페이지를 0.001초 동안 처리했습니다(0.000MB/초).

    */

    SELECT * FROM SYS.DATABASES WHERE NAME ='PageLevelRestores'

 

    -- Finally finish with the restore sequence

    RESTORE DATABASE PageLevelRestores WITH RECOVERY

    GO

 

    /*

    RESTORE DATABASE이(가) 0개의 페이지를 0.002초 동안 처리했습니다(0.000MB/초).

    */

 

    use master

    go

 

    select * from PageLevelRestores.dbo.test

 

    완료하였음.

    정확한 아키텍쳐를 모르겠다.

 

 

[참고]

Curruption Page 상태에서 아래와 같이 CheckDB, repair_allow_data_loss 를 하게되면

해당 페이지의 데이터는 복원 실패가 대부분일 것이다.

그래서 사전에 장애인지 시점에 전체백업(COPY_ONLY)로 백업을 해두면 좋을 것 같다.

백업본으로 여러가지 방법으로 복원하여 최소한 데이터 누락을 막기 위함이다.

 

 

    -- 일단 돌려보자

    ALTER DATABASE PageLevelRestores_T SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    GO

    dbcc checkdb('PageLevelRestores_T', repair_allow_data_loss)

 

    /*

    복구: 페이지 (1:118)이(가) 개체 ID 245575913, 인덱스 ID 0, 파티션 ID 72057594040549376, 할당 단위 ID 72057594045792256(In-row data 유형)에서 할당 취소되었습니다.

    메시지 8928, 수준 16, 상태 1, 줄 106

    개체 ID 245575913, 인덱스 ID 0, 파티션 ID 72057594040549376, 할당 단위 ID 72057594045792256(In-row data 유형): 페이지 (1:118)을(를) 처리할 수 없습니다. 자세한 내용은 다른 오류를 참조하십시오.

            오류가 복구되었습니다.

    메시지 8939, 수준 16, 상태 98, 줄 106

    테이블 오류: 개체 ID 245575913, 인덱스 ID 0, 파티션 ID 72057594040549376, 할당 단위 ID 72057594045792256(In-row data 유형), 페이지 (1:118). 테스트(IS_OFF (BUF_IOERR, pBUF->bstat))에 실패했습니다. 값은 2057 및 -4입니다.

            오류가 복구되었습니다.

 

 

    CHECKDB이(가) 데이터베이스 'PageLevelRestores_T'에서 0개의 할당 오류와 2개의 일관성 오류를 찾았습니다.

    CHECKDB이(가) 데이터베이스 'PageLevelRestores_T'에서 0개의 할당 오류와 2개의 일관성 오류를 수정했습니다.

 

    */

 

 

    use master

    go

 

    select * from PageLevelRestores_T.dbo.test

    /*

    CCCCC 레코드가 전부 사라진것을 확인할 수 있다.

    */

 

 

[참고문서]

How to perform a Page Level Restore in SQL Server

http://www.sqlpassion.at/archive/2015/10/13/how-to-perform-a-page-level-restore-in-sql-server/