보미네

backup log dbname to disk ='c:\xxx' with norecovery, init, copy_only 옵션을 주면 데이터베스가 복원중 으로 변한다.  

copy 이후 실제 서버의 transaction log 를 apply 시켜 restore 를 했다.

emergency 모드에서 데이터를 확인해 봤다. 여기서 확인되는 데이터만 살릴수 있고,
delete update 는 잘 적용 되어 있고, insert 는 적용되지 않은것을 알 수 있다.

리커버리 후 상태이다.

결국 PFS, GAM만 복구하면 입력한 모든 데이터를 살릴수 있을듯도 하다.
본 테스트에서는 PFS 만 깨어졌지만 insert 용량이 크면 GAM 페이지도 깨어졌다.
그러므로 파일 copy 를 이용한 recovery 모드 변경후 transaction apply 시나리오는 불완전 복구만 가능하다.

이번 테스트를 통해서 비상로그 백업의 용도를 정확히 인지 할 수 있다.

오라클 데이터베이스의 경우 Hot Backup 모드를 지원한다. begin backup 모드로 들어가고 파일 복사 +  log apply 를 통해 데이터베이스를 완전한 상태로 복구 할 수 있다. 정말 부럽지 않은가?

SQL Server 아직 멀었다. 이게 쓸만하게 되려면 한 10년쯤 지나야 하지 않을까 싶다. 그때도 난 SQL 할꺼다...^^; 취미니깐



스크립트는 아래를 참조 한다.


use master
go

alter database CopyOnlyRecovery set single_user with rollback immediate
go

drop database CopyOnlyRecovery
go

use master
go

create database CopyOnlyRecovery
go

use CopyOnlyRecovery
go

create table tblx
(idx int identity(1,1)
,c1 char(5000)
)
go

create clustered index cl_tblx on tblx (idx)
go

insert into tblx values (1)
go 100

select top 1 * from tblx
go

backup database CopyOnlyRecovery to disk ='c:\f1.bak' with init
go

insert into tblx values (2)
go 100

backup log CopyOnlyRecovery to disk ='c:\l1.bak' with init
go

select top 1 * from tblx order by c1 desc
go

insert into tblx values (3)
go 100

select top 1 * from tblx order by c1 desc

checkpoint
go

select * from tblx

-- sqlserver stop
-- 위와 같은 상황에서 database shutdown 후 CopyOnlyRecovery 의 mdf 와 ldf 를 다른곳으로 copy!
-- sqlserver start

use CopyOnlyRecovery
go

insert into tblx values (4)
go 100

update tblx set c1 = 111 where idx = 1
go

select * from tblx
go

backup log CopyOnlyRecovery to disk ='c:\l2.bak' with init
go

insert into tblx values (5)
go 100

backup log CopyOnlyRecovery to disk ='c:\l3.bak' with init
go

update tblx set c1 = 222 where idx = 2
go

delete from tblx where idx = 3
go

select * from tblx where idx < 10

backup log CopyOnlyRecovery to disk ='c:\l4.bak' with init
go

checkpoint

-- 이제부터 예전 copy 한 파일로 부터 복원을 해보자.

--use master
--go
-- ***************************************************
-- 예전에 복사해둔 데이터베이스를 attach 한다. 
-- stop
-- copy
-- start

use master
go

-- 아래 명령을 날리면 3까지 복원된 상태에서 norecovery 모드로 변경된다. 마치 full backup 에서 복원한것 처럼
-- 사실 아래의 명령어는 mission critical server 에서 log backup 후 더이상의 transaction 이 일어나지 못하게
-- 막는 옵션이다.

alter database CopyOnlyRecovery set single_user with rollback immediate
go

backup log CopyOnlyRecovery to disk = 'c:\fail2.bak' with norecovery , init , COPY_ONLY
go

-- 데이터베이스는 복구중 모드로 변경된다. 여기에서 이후 로그를 어플라이 시킨다.

restore log CopyOnlyRecovery from disk = 'c:\fail2.bak' with norecovery
restore log CopyOnlyRecovery from disk = 'c:\l2.bak' with norecovery , continue_after_error
restore log CopyOnlyRecovery from disk = 'c:\l3.bak' with norecovery , continue_after_error
restore log CopyOnlyRecovery from disk = 'c:\l4.bak' with norecovery , continue_after_error
restore database CopyOnlyRecovery with recovery , continue_after_error

use master
go

alter database CopyOnlyRecovery set emergency
go

use CopyOnlyRecovery
go

select count(*) from tblx
go
-- 299

select top 10 * from tblx where idx < 10 order by idx
go

ALTER DATABASE CopyOnlyRecovery SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go

dbcc checkdb ('CopyOnlyRecovery', repair_allow_data_loss)
go

현재 데이터베이스를 다시 시작하지 못했습니다. 현재 데이터베이스가 master로 전환됩니다.
경고: 데이터베이스 'CopyOnlyRecovery'의 로그가 다시 작성되었습니다. 트랜잭션에 일관성이 없습니다. RESTORE 체인이 끊어져 서버에 이전 로그 파일에 대한 컨텍스트가 더 이상 없으므로 해당 로그 파일이 어떤 파일인지 알아야 합니다. DBCC CHECKDB를 실행하여 물리적 일관성을 확인해야 합니다. 데이터베이스가 dbo 전용 모드로 전환되었습니다. 데이터베이스를 사용할 수 있는 준비가 되면 데이터베이스 옵션을 다시 설정하고 모든 추가 로그 파일을 삭제하십시오.
메시지 2510, 수준 16, 상태 17, 줄 1
DBCC checkdb 오류: This system table index cannot be recreated.
'CopyOnlyRecovery'의 DBCC 결과입니다.
Service Broker 메시지 9675, 상태 1: 분석된 메시지 유형: 14.
Service Broker 메시지 9676, 상태 1: 분석된 서비스 계약: 6.
Service Broker 메시지 9667, 상태 1: 분석된 서비스: 3.
Service Broker 메시지 9668, 상태 1: 분석된 서비스 큐: 3.
Service Broker 메시지 9669, 상태 1: 분석된 대화 끝점: 0.
Service Broker 메시지 9674, 상태 1: 분석된 대화 그룹: 0.
Service Broker 메시지 9670, 상태 1: 분석된 원격 서비스 바인딩: 0.
Service Broker 메시지 9605, 상태 1: 대화 우선 순위 분석: 0.
'sys.sysrscols'의 DBCC 결과입니다.
7개 페이지에 개체 "sys.sysrscols"에 대한 행이 634개 있습니다.
'sys.sysrowsets'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysrowsets"에 대한 행이 92개 있습니다.
'sys.sysallocunits'의 DBCC 결과입니다.
2개 페이지에 개체 "sys.sysallocunits"에 대한 행이 104개 있습니다.
'sys.sysfiles1'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysfiles1"에 대한 행이 2개 있습니다.
'sys.syspriorities'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.syspriorities"에 대한 행이 0개 있습니다.
'sys.sysfgfrag'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysfgfrag"에 대한 행이 2개 있습니다.
'sys.sysphfg'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysphfg"에 대한 행이 1개 있습니다.
'sys.sysprufiles'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysprufiles"에 대한 행이 2개 있습니다.
'sys.sysftinds'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysftinds"에 대한 행이 0개 있습니다.
'sys.sysowners'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysowners"에 대한 행이 14개 있습니다.
'sys.sysprivs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysprivs"에 대한 행이 130개 있습니다.
'sys.sysschobjs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysschobjs"에 대한 행이 54개 있습니다.
'sys.syscolpars'의 DBCC 결과입니다.
8개 페이지에 개체 "sys.syscolpars"에 대한 행이 485개 있습니다.
'sys.sysnsobjs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysnsobjs"에 대한 행이 1개 있습니다.
'sys.syscerts'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.syscerts"에 대한 행이 0개 있습니다.
'sys.sysxprops'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysxprops"에 대한 행이 0개 있습니다.
'sys.sysscalartypes'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysscalartypes"에 대한 행이 34개 있습니다.
'sys.systypedsubobjs'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.systypedsubobjs"에 대한 행이 0개 있습니다.
'sys.sysidxstats'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysidxstats"에 대한 행이 111개 있습니다.
'sys.sysiscols'의 DBCC 결과입니다.
메시지 8948, 수준 16, 상태 1, 줄 1
데이터베이스 오류: 페이지 (1:93)이(가) PFS 페이지 (1:1)에서 잘못된 유형으로 표시되어 있습니다. PFS 상태 0x0에는 0x60이(가) 필요합니다.
        오류가 복구되었습니다.
메시지 8948, 수준 16, 상태 1, 줄 1
데이터베이스 오류: 페이지 (1:94)이(가) PFS 페이지 (1:1)에서 잘못된 유형으로 표시되어 있습니다. PFS 상태 0x0에는 0x60이(가) 필요합니다.
        오류가 복구되었습니다.
2개 페이지에 개체 "sys.sysiscols"에 대한 행이 262개 있습니다.
CHECKDB이(가) 테이블 'sys.sysiscols'(개체 ID 55)에서 2개의 할당 오류와 0개의 일관성 오류를 찾았습니다.
CHECKDB이(가) 테이블 'sys.sysiscols'(개체 ID 55)에서 2개의 할당 오류와 0개의 일관성 오류를 수정했습니다.
'sys.sysbinobjs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysbinobjs"에 대한 행이 23개 있습니다.
'sys.sysaudacts'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysaudacts"에 대한 행이 0개 있습니다.
'sys.sysobjvalues'의 DBCC 결과입니다.
복구: 데이터베이스 "CopyOnlyRecovery"에서 개체 "sys.sysobjvalues"의 Clustered 인덱스가 다시 작성되었습니다.
메시지 8945, 수준 16, 상태 1, 줄 1
테이블 오류: 개체 ID 60, 인덱스 ID 1을(를) 다시 작성합니다.
        오류가 복구되었습니다.
메시지 8976, 수준 16, 상태 1, 줄 1
테이블 오류: 개체 ID 60, 인덱스 ID 1, 파티션 ID 281474980642816, 할당 단위 ID 281474980642816(In-row data 유형). 부모 (1:142)과(와) 이전의 (1:152)이(가) 페이지 (1:568)을(를) 참조하지만 이 페이지가 검색에 없습니다. 이전 오류를 확인하십시오.
        오류가 복구되었습니다.
메시지 8980, 수준 16, 상태 1, 줄 1
테이블 오류: 개체 ID 60, 인덱스 ID 1, 파티션 ID 281474980642816, 할당 단위 ID 281474980642816(In-row data 유형). 인덱스 노드 페이지 (1:142), 슬롯 16에서 자식 페이지 (1:569)과(와) 이전 자식 페이지 (1:568)을(를) 참조하지만 해당 페이지가 없습니다.
        오류가 복구되었습니다.
메시지 8980, 수준 16, 상태 1, 줄 1
테이블 오류: 개체 ID 60, 인덱스 ID 1, 파티션 ID 281474980642816, 할당 단위 ID 281474980642816(In-row data 유형). 인덱스 노드 페이지 (1:142), 슬롯 17에서 자식 페이지 (1:570)과(와) 이전 자식 페이지 (1:569)을(를) 참조하지만 해당 페이지가 없습니다.
        오류가 복구되었습니다.
15개 페이지에 개체 "sys.sysobjvalues"에 대한 행이 89개 있습니다.
CHECKDB이(가) 테이블 'sys.sysobjvalues'(개체 ID 60)에서 0개의 할당 오류와 3개의 일관성 오류를 찾았습니다.
CHECKDB이(가) 테이블 'sys.sysobjvalues'(개체 ID 60)에서 0개의 할당 오류와 3개의 일관성 오류를 수정했습니다.
'sys.sysclsobjs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysclsobjs"에 대한 행이 16개 있습니다.
'sys.sysrowsetrefs'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysrowsetrefs"에 대한 행이 0개 있습니다.
'sys.sysremsvcbinds'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysremsvcbinds"에 대한 행이 0개 있습니다.
'sys.sysxmitqueue'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysxmitqueue"에 대한 행이 0개 있습니다.
'sys.sysrts'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysrts"에 대한 행이 1개 있습니다.
'sys.sysconvgroup'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysconvgroup"에 대한 행이 0개 있습니다.
'sys.sysdesend'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysdesend"에 대한 행이 0개 있습니다.
'sys.sysdercv'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysdercv"에 대한 행이 0개 있습니다.
'sys.syssingleobjrefs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.syssingleobjrefs"에 대한 행이 146개 있습니다.
'sys.sysmultiobjrefs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysmultiobjrefs"에 대한 행이 106개 있습니다.
'sys.sysguidrefs'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysguidrefs"에 대한 행이 0개 있습니다.
'sys.syscompfragments'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.syscompfragments"에 대한 행이 0개 있습니다.
'sys.sysftstops'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysftstops"에 대한 행이 0개 있습니다.
'sys.sysqnames'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysqnames"에 대한 행이 97개 있습니다.
'sys.sysxmlcomponent'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysxmlcomponent"에 대한 행이 99개 있습니다.
'sys.sysxmlfacet'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysxmlfacet"에 대한 행이 112개 있습니다.
'sys.sysxmlplacement'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysxmlplacement"에 대한 행이 18개 있습니다.
'sys.sysobjkeycrypts'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysobjkeycrypts"에 대한 행이 0개 있습니다.
'sys.sysasymkeys'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.sysasymkeys"에 대한 행이 0개 있습니다.
'sys.syssqlguides'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.syssqlguides"에 대한 행이 0개 있습니다.
'sys.sysbinsubobjs'의 DBCC 결과입니다.
1개 페이지에 개체 "sys.sysbinsubobjs"에 대한 행이 3개 있습니다.
'sys.syssoftobjrefs'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.syssoftobjrefs"에 대한 행이 0개 있습니다.
'sys.queue_messages_1977058079'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.queue_messages_1977058079"에 대한 행이 0개 있습니다.
'sys.queue_messages_2009058193'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.queue_messages_2009058193"에 대한 행이 0개 있습니다.
'sys.queue_messages_2041058307'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.queue_messages_2041058307"에 대한 행이 0개 있습니다.
'sys.filestream_tombstone_2073058421'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.filestream_tombstone_2073058421"에 대한 행이 0개 있습니다.
'sys.syscommittab'의 DBCC 결과입니다.
0개 페이지에 개체 "sys.syscommittab"에 대한 행이 0개 있습니다.
'tblx'의 DBCC 결과입니다.
메시지 8914, 수준 16, 상태 1, 줄 1
개체 ID 2105058535, 인덱스 ID 0, 파티션 ID 72057594038779904, 할당 단위 ID 72057594039828480(In-row data 유형)의 페이지 (1:77)에 대한 사용 가능한 PFS 공간 정보가 잘못되었습니다. 값   0_PCT_FULL이(가) 필요한데 실제 값은  80_PCT_FULL입니다.
        오류가 복구되었습니다.
300개 페이지에 개체 "tblx"에 대한 행이 299개 있습니다.
CHECKDB이(가) 테이블 'tblx'(개체 ID 2105058535)에서 0개의 할당 오류와 1개의 일관성 오류를 찾았습니다.
CHECKDB이(가) 테이블 'tblx'(개체 ID 2105058535)에서 0개의 할당 오류와 1개의 일관성 오류를 수정했습니다.
CHECKDB이(가) 데이터베이스 'CopyOnlyRecovery'에서 2개의 할당 오류와 4개의 일관성 오류를 찾았습니다.
CHECKDB이(가) 데이터베이스 'CopyOnlyRecovery'에서 2개의 할당 오류와 4개의 일관성 오류를 수정했습니다.
DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

use CopyOnlyRecovery
go

alter database CopyOnlyRecovery set multi_user
go

select top 100 * from tblx order by idx 

select count(*) from tblx

select * from tblx

 
-- 기타
DBCC SHOWFILESTATS
DBCC EXTENTINFO(CopyOnlyRecovery, TBLX, -1)
DBCC TRACEON(3604)
go
DBCC PAGE('CopyOnlyRecovery',1, 1,2)
go
EXEC SP_RESETSTATUS 'CopyOnlyRecovery';
go
dbcc checkdb (CopyOnlyRecovery)
go
use master
go

restore headeronly from disk = 'e:\backupProject20121108.bak'
restore filelistonly from disk = 'e:\backupProject20121108.bak'

restore database backupProject from disk = 'e:\backupProject20121108.bak'
with recovery
, move 'backupProject' to 'l:\mssql\backupProject.mdf'
, move 'backupProject_log' to 'l:\mssql\backupProject_log.ldf'

Next up in the Inside the Storage Engine series is a discussion of page structure. Pages exist to store records. A database page is an 8192-byte (8KB) chunk of a database data file. They are aligned on 8KB boundaries within the data files, starting at byte-offset 0 in the file. Here's a picture of the basic structure:

Header

The page header is 96 bytes long. What I'd like to do in this section is take an example page header dump from DBCC PAGE and explain what all the fields mean. I'm using the database from the page split post and I've snipped off the rest of the DBCC PAGE output.

DBCC

TRACEON (3604)

DBCC

PAGE ('pagesplittest', 1, 143, 1);

GO

m_pageId = (1:143) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 68 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594042384384
Metadata: PartitionId = 72057594038386688 Metadata: IndexId = 1
Metadata: ObjectId = 2073058421 m_prevPage = (0:0) m_nextPage = (1:154)
pminlen = 8 m_slotCnt = 4 m_freeCnt = 4420
m_freeData = 4681 m_reservedCnt = 0 m_lsn = (18:116:25)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 1333613242

Note that I didn't include the fields starting with Metadata:. That's because they're not part of a page header. During SQL Server 2005 development I did some major work rewriting the guts of DBCC PAGE and to save everyone using it from having to do all the system table lookups to determine what the actual object and index IDs are, I changed DBCC PAGE to do them internally and output the results.

Records

See this blog post for details.

Slot Array

It's a very common misconception that records within a page are always stored in logical order. This is not true. There is another misconception that all the free-space in a page is always maintained in one contiguous chunk. This also is not true. (Yes, the image above shows the free space in one chunk and that very often is the case for pages that are being filled gradually.)

If a record is deleted from a page, everything remaining on the page is not suddenly compacted - inserters pay the cost of compaction when its necessary, not deleters.

Consider a completely full page - this means that record deletions cause free space holes within the page. If a new record needs to be inserted onto the page, and one of the holes is big enough to squeeze the record into, why go to the bother of comapcting it? Just stick the record in and carry on. What if the record should logically have come at the end of all other records on the page, but we've just inserted it in the middle - doesn't that screw things up somewhat?

No, because the slot array is ordered and gets reshuffled as records are inserted and deleted from pages. As long as the first slot array entry points to the logically first record on the page, everything's fine. Each slot entry is just a two-byte pointer into the page - so its far more efficient to manipulate the slot array than it is to manipulate a bunch of records on the page. Only when we know there's enough free space contained within the page to fit in a record, but its spread about the page do we compact the records on the page to make the free space into a contiguous chunk.

One interesting fact is that the slot array grows backwards from the end of the page, so the free space is squeezed from the top by new rows, and from the bottom by the slot array.