SQL2005 복원의 새로운점

Posted 2008. 10. 24. 04:57


복원의 새로운 점


온라인 복원
SQL Server 2005 엔터프라이즈 에디션에는 백업으로부터 온라인상에서 복원할 수 있는 새로운 기술을 장착했습니다. 이러한 기능은 2000에서는 모든 파일이 복원이 되어야 서비스가 되었던 것에 비해 서비스 중지시간을 줄일 수 있습니다. (단 전체복구 모델과 대량복구모델에 대해서만 가능합니다.)

가능환경
1. 엔터프라이즈 에디션과 디벨로퍼 에디션에서만 지원합니다.
2. 다수의 파일그룹을 가지고 있는 데이터베이스에 해당됩니다.
3. Secondary 파일그룹이 복원되는 동안만 접근이 불가능합니다.
4. 파일그룹이 복원될 때, 모든 파일그룹이 복원이 되어야만 데이터베이스의 일관성이 유지됩니다.

2000와 마찬가지로 2005에서 오프라인 복원작업을 그대로 하실 수 있습니다. 또한 SQL Server Management Studio를 이용해서 데이터베이스와 로그를 복원하실 수 있습니다.

2005에서는 2000 및 7.0에서 백업사본에 대해서는 오프라인 복원만 지원합니다. 온라인 복원을 수행하기 위해서는 2005에서 백업된 소스를 이용해야 합니다.

2005의 RESTORE 명령어는 새로운 옵션 RESTRICTED_USER을 포함하고 있습니다. 이 옵션은 복원된 데이터베이스에 db_owner, dbcreator, sysadmin 역할을 가진 구성원에게 제한적으로 접근을 하게 합니다.

온라인 복원은 두 가지 레벨로 지원됩니다.

Page Online Restore(페이지단위 온라인 복원)
온라인 복원의 특징은 문제가 있는 페이지를 고립시켜서 이 문제된 페이지가 다른 정상적인 데이터베이스를 복원하는데 있어 그 영향을 거의 주지 않는 다는 것입니다. 손상된 페이지는 데이터베이스로그에 남겨지게 됩니다.

손상된 페이지에 접근을 하면 에러가 발생 할 것이며 에러로그에 그 결과가 남게 될 것입니다. 이렇게 얻어진 정보를 통해서 페이지의 위치를 알아내고 유용한 백업자료에서 복원을 하게 됩니다.

[페이지 복원 절차]

--1) 손상된 페이지의 ID를 찾아냅니다.
SELECT * FROM msdb..suspect_page_table

--2) 트랜잭션로그의 진행중인 부분을 백업합니다.
BACKUP LOG AdventureWorks
TO DISK =‘ Active_Log.bak’WITH NO_TRUNCATE

--3) 가장 최근의 전체 백업이나 차등백업으로부터 손상된 페이지를 복원합니다.
RESTORE DATABASE AdventureWorks PAGE =‘ 3:3241’
FROM DISK =‘ ...’WITH NORECOVERY

--4) 로그백업을 수행하고 데이터베이스를 복원합니다.
RESTORE LOG AdventureWorks
FROM DISK =‘ ...’WITH NORECOVERY
.
.
.
RESTORE LOG AdventureWorks
FROM DISK =‘ ...’WITH RECOVERY

--5) 수행중인 로그백업을 사용해서 복원합니다.
RESTORE LOG AdventureWorks
FROM DISK =‘ Active_Log.bak’
WITH RECOVERY

 

 

파일 단위 온라인 복원
데이터베이스의 파일그룹에서 어느 한 파일이라도 복원 중에 있다면 전체 파일 그룹은 오프라인 상태에 있게 됩니다. 그래서 주 파일그룹에 있는 하나의 파일이라도 복원 중이면 전체 데이터베이스는 오프라인상태가 됩니다. 하지만 주파일 그룹의 복구가 끝나면 데이터베이스는 온라인상태가 되며 각각의 이차파일그룹(Secondary filegroup)이 복원이 되면 해당 파일 그룹은 자동으로 온라인이 됩니다.

[파일단위 복원 절차]

--1) 활성중인 트랜잭션로그 백업하기
BACKUP LOG AdventureWorks
TO DISK =‘ Active_Log.bak’WITH NO_TRUNCATE

--2) 손상된 파일 복원하기
RESTORE DATABASE AdventureWorks FILE =‘ Logical_Damaged_Filename’
FROM DISK =‘ ...’WITH NORECOVERY

--3) 트랜잭션 로그 복원하기
RESTORE LOG AdventureWorks
FROM DISK =‘ ...’WITH NORECOVERY

--4) 활성중인 트랜잭션 로그를 복원하고 해당 데이터베이스 복구하기.
RESTORE LOG AdventureWorks
FROM DISK =‘ Active_Log.bak’
WITH RECOVERY


Piecemeal 복원
2005 엔터프라이즈 에디션에서는 Piecemeal 복원을 지원합니다. 이 복원은 파일그룹단위로 복원을 진행합니다. 이때 복원되는 그룹단위로 온라인 상태로 됩니다. 이전 버전에서는 모든 파일 그룹이 복원이 되기 때문에 우선적으로 온라인 상태가 되어야 하는 경우 복원시간이 많이 소요되었습니다. 예를 들어 파일그룹하나에 문제가 있는 경우 다른 파일그룹에까지 영향을 미치게 되어 복원에 문제가 발생할 수 있습니다.

2005에서 이 Piecemeal 복원을 이용하면 중요도 높은 파일 그룹부터 복원하고 서비스를 할 수 있게 됩니다.


[따라하기] Piecemeal 복원

USE master
GO
DROP DATABASE piecemealTest
GO
-- 테스트용데이터베이스생성
CREATE DATABASE [piecemealTest] ON PRIMARY
( NAME = N’piecemealTest’, FILENAME = N’c:₩temp₩piecemealTest.mdf’,
SIZE = 3MB , FILEGROWTH = 10%),
FILEGROUP [second]
( NAME = N’second’, FILENAME = N’c:₩temp₩second.ndf’, SIZE = 3MB ,
FILEGROWTH = 10%),
FILEGROUP [third]
( NAME = N’third’, FILENAME = N’c:₩temp₩third.ndf’, SIZE = 3MB ,
FILEGROWTH = 10%)
LOG ON
( NAME = N’ piecemealTest_log’ , FILENAME = N’ c:₩ temp
₩piecemealTest_log.ldf’, SIZE = 1024KB , FILEGROWTH = 1024KB )
GO
USE piecemealTest
GO

-- 데이터베이스의기본파일그룹을변경합니다.
ALTER DATABASE piecemealTest
modify FILEGROUP second default
GO

-- od라는테이블을northwind 데이터베이스에있는order details 테이블을이용하여만듭니다.
SELECT TOP 100
productid, quantity
INTO od
FROM northwind..biGOd
GO

-- 주파일그룹에테이블odonPrimary를생성합니다.
CREATE table odonPrimary
(productID int
,quantity int
) on [primary]
GO

-- 세번째파일그룹에테이블od3rd를생성합니다.
CREATE table od3rd
(productID int
,quantity int
) on THIRD
GO

-- 위에서생성한테이블에값을넣습니다.
INSERT INTO od3rd
      SELECT productID, quantity FROM od
INSERT INTO odOnPrimary
      SELECT productID, quantity FROM od
GO

-- 백업장치dumpPiece와dumpPieceLog를생성합니다.
exec sp_addumpdevice’disk’,’dumpPiece’,’c:₩temp₩dumpPiece.bak’
exec sp_addumpdevice’disk’,’dumpPieceLog’,’c:₩temp₩dumpPieceLog.bak’
GO
-- 생성확인을합니다.
sp_helpdevice

SELECT TOP 100 * FROM od

-- od 테이블(second file group)에값을변경합니다.
update od -- second group
set quantity=2000
where productID=11
GO

-- od3rd 테이블(third file group)에값을변경합니다.
update od3rd --third group
set quantity=3000
where productID=11
GO

-- odOnPrimary 테이블(Primary file group)에값을변경합니다.
update odOnPrimary -- Primary group
set quantity=1000
where productID=11
GO

-- 전체백업을수행합니다.
BACKUP DATABASE piecemealTest to dumpPiece WITH init
GO
-- 로그백업을수행합니다.
BACKUP log piecemealTest to dumpPiecelog WITH init
GO

USE master
GO

RESTORE DATABASE piecemealTest
FILEGROUP=’Primary’--FILEGROUP=’Primary’
FROM dumpPiece WITH partial,norecovery,replace

RESTORE DATABASE piecemealTest
FROM dumpPiece WITH partial,norecovery,replace

RESTORE log piecemealTest FROM dumpPieceLog
GO

USE piecemealTest
GO
SELECT * FROM odOnPrimary
GO
SELECT * FROM od
GO
SELECT * FROM od3rd
GO
--결과메시지
--복원은Primary 에서이루어졌기때문에
--Second에있는od 테이블과third에있는od3rd는온라인상태가 되지못했다.
(100개행적용됨)
메시지8653, 수준16, 상태1, 줄1
테이블’od’이(가) 온라인상태가아닌파일그룹에있어서쿼리프로세서에서이테이블또는뷰에대한계획을생성할수없습니다.

메시지8653, 수준16, 상태1, 줄1
테이블’od3rd’이(가) 온라인상태가아닌파일그룹에있어서쿼리프로세서에서이테이블또는뷰에대한계획을생성할수없습니다.

SQL 2000에서는 위까지의 과정을 수행하면 모든 파일이 복구 됩니다.

USE master
GO
BACKUP log piecemealTest to disk =‘ c:₩temp₩log1’WITH no_truncate, init

-- 이번에는Second 그룹에있는파일을복원합니다.
RESTORE DATABASE piecemealTest
FILEGROUP=’SECOND’
FROM dumpPiece
WITH partial,norecovery
GO
RESTORE log piecemealTest FROM dumpPieceLog

USE piecemealTest
GO
SELECT * FROM odOnPrimary
GO
SELECT * FROM od
GO
SELECT * FROM od3rd
GO
-- 결과에서Primary 그룹과Second 그룹은복원이되었고
-- third 그룹은 아직접근할수없다.
(100개행적용됨)

(100개행적용됨)
메시지8653, 수준16, 상태1, 줄1
테이블’od3rd’이(가) 온라인상태가아닌파일그룹에있어서쿼리프로세서에서이테이블또는뷰에대한계획을생성할수없습니다.


응급데이터베이스 상태
백업매체의 문제로 인한 복원의 실패나 문제가 있는 데이터베이스를 복구하려 할 때 ALTER DATABASE 명령문에 EMERGENCY 문을 추가해서 데이터베이스를 응급상태로 전환 시킬 수 있습니다.

데이터베이스가 주의상태로 표시된다면 데이터베이스의 상태를 응급으로 변경할 수 있습니다. 이렇게 변경을 하면 데이터베이스는 읽기전용으로 바꾸거나 데이터베이스를 분리할 수 있습니다.

응급상태로 전환을 하게되면 데이터베이스는 단독접근(Single user)이 되고 sysadmin 서버 역할을 가진 로그인만 접속 가능합니다. 그리고, 읽기전용데이터베이스가 됩니다. 이런 상태에서 데이터베이스의 복구를 가능하게 합니다.

[따라하기] AdventureWorks 데이터베이스 응급상태로 전환하기

USE master
go

ALTER DATABASE AdventureWorks
SET EMERGENCY
GO



-- 데이터베이스의 특성을 살펴봅니다.
SELECT DATABASEPROPERTYEX‘( AdventureWorks’,‘ Status’)
SELECT name, state, state_desc FROM sys.databases



USE AdventureWorks
GO

SELECT * FROM sales.Store

BEGIN TRAN
Go

--아래의문장은실패합니다.
UPDATE sales.Store SET name =‘ X’+name
go

ROLLBACK

메시지3908, 수준16, 상태1, 줄3
데이터베이스’adventureworks’은(는) 복구무시모드이므로BEGIN TRANSACTION을 실행할 수 없습니다.
문이 종료되었습니다.

메시지3903, 수준16, 상태1, 줄2

--아래의 쿼리도 실패합니다.
BACKUP DATABASE AdventureWorks TO DISK=’c:₩temp₩AWBACK.bak’
GO

메시지3033, 수준16, 상태0, 줄3
응급모드에서 열린 데이터베이스에서는BACKUP DATABASE를 사용할 수 없습니다.
메시지3013, 수준16, 상태1, 줄3
BACKUP DATABASE이(가) 비정상적으로 종료됩니다.

데이터베이스의 상태를 정상적으로 복구합니다.
use master
go
ALTER DATABASE AdventureWorks SET ONLINE
GO


ATTACH_REBUILD_LOG 절
OLTP환경에서 대용량 데이터베이스를 가지고 있다고 생각해봅시다. 이 데이터베이스를 다른 서버에 읽기위주의 용도로 옮긴다고 생각을 하면 단지 데이터파일만 옮기면 됩니다. 그러기 위해서는 서비스를 중단시키고 .mdf 파일과 .ndf 파일을 복사해서 옮깁니다. 그 후 CREATE DATABASE 명령어에서 ATTACH_REBUILD_LOG 절을 추가하여 수행합니다. 이렇게 하면 SQL Server는 데이터베이스를 연결하면서 새롭게 로그를 생성합니다.

완벽하게 수행하기 위해서는 이 작업이 끝난 뒤 바로 전체 백업을 수행해야 합니다. 이전 버전의 sp_attach_single_file_db 저장 프로시저는 호환성을 위해서만 사용됩니다.

2005에서는CREATE DATABASE 문장에 ATTACH_REBUILD_LOG을 사용할 것을 권장합니다.

문법은 그 예제는 다음과 같습니다.
CREATE DATABASE database_name
ON <filespec> [ ,...n ]
FOR { ATTACH [ WITH <service_broker_option> ]
| ATTACH_REBUILD_LOG }

예제)
CREATE DATABASE AdventureWorks
FOR ATTACH_REBUILD_LOG
데이터베이스 스냅숏에는 ATTACH_REBUILD_LOG옵션을 사용할 수 없습니다.