backup log with norecovery?
sql server 를 full backup 을 이용하지 않고 복구 할 수 있을까?
그냥 단순한 copy 본으로 recovery 모드로 변경 후 transaction log 를 계속 복구하면 어떻게 될까?
테스트 이다.
update delete 는 복구가 가능하며, file copy 시점 이후의 insert 데이터는 살리지 못한다.
그러나 binary 파일을 열어보면 pfs 페이지와 gam 페이지의 결함으로 출력하지 못한다는 것을 알 수 있고
이 후 복구 방법을 스크립트화 했다.
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'