2007~2011/SQL Server2009. 10. 3. 00:01
SQL Server 스냅숏(snapshot)이란 특정 시점의 변경 불가능한 읽기 전용 데이터베이스를 의미합니다. AdventureWorks 데이터베이스를 대상으로 스냅숏을 생성하고 테스트를 통해 어떻게 동작하는지 간단히 살펴 보고자 아래와 같이 정리하였습니다.


[환경]
SQL Server 2008 SP1

1. 스냅숏 생성 
CREATE DATABASE AdventureWorks_snapshot ON 
(NAME = AdventureWorks_Data, FILENAME = 'D:\AdventureWorks_snap.mdf')
AS SNAPSHOT OF AdventureWorks 
GO

NAME 의 값은 AdventureWorks 데이터베이스의 mdf 파일 logical name(AdventureWorks_Data) 이며, 읽기 전용이기 때문에 트랜잭션 로그 파일이 필요하지 않습니다. 물론 테이블 생성 및 데이터 입력, 변경, 삭제는 가능하지 않습니다. 

sp_helpdb 로 snapshot 데이터베이스 정보를 조회하면 아래와 같은 결과를 볼 수 있습니다. 데이터, 로그 파일의 위치가 원본 데이터베이스를 바라보고 있음을 확인할 수 있습니다. 




2. 스냅숏의 데이터 조회 및 변경 
 가. 테이블을 조회 
USE AdventureWorks_snapshot 
GO

SELECT * FROM Person.Address WHERE AddressID < 10
-- 정상적으로 1~9까지의 데이터 조회가 가능합니다.

 나. 데이터 삭제를 시도하면 오류가 발생합니다.
DELETE FROM Person.Address WHERE AddressID < 10

Msg 3906, Level 16, State 1, Line 1
데이터베이스 "AdventureWorks_snapshot"은(는) 읽기 전용이므로 업데이트할 수 없습니다.


3. 원본 데이터베이스 데이터 변경 
USE AdventureWorks 
GO

UPDATE Person.Address SET AddressLine2 = '1111' WHERE AddressID < 10
-- (9 row(s) affected) 
SELECT * FROM Person.Address WHERE AddressID < 10


USE AdventureWorks_snapshot
GO

SELECT * FROM Person.Address WHERE AddressID < 10 

위 쿼리를 통해 결국 원본 데이터베이스의 데이터를 변경하더라도 스냅숏은 변경되지 않는다는 것을 확인할 수 있습니다. 


4. 삭제된 데이터 또는 테이블을 스냅숏으로부터 복원
삭제된 데이터를 복원하기 위해서는 INSERT INTO, 삭제된 테이블을 복원하기 위해서는 SELECT INTO 명령을 사용하여 스냅숏 데이터베이스로부터 복원할 수 있습니다. 별도 예제를 정리하지 않았습니다.


5. 스냅숏 데이터베이스로부터 원본 데이터베이스 복원
원본 데이터베이스의 파일이 깨지지 않았을 경우에나 가능한 방법입니다. 삭제된 테이블, 삭제된 데이터가 많을 경우 유용할 듯 합니다. 
USE MASTER
GO

RESTORE DATABASE AdventureWorks FROM DATABASE_SNAPSHOT='AdventureWorks_snapshot' WITH REPLACE


6. 원본 데이터베이스 삭제
USE MASTER
GO

DROP DATABASE AdventureWorks 

스냅숏이 설정되어 있는 상태에서는 원본 데이터베이스를 삭제할 수 없습니다. 삭제 시도 시 아래 오류가 발생합니다. 삭제를 위해서는 먼저 Snapshot 데이터베이스를 삭제해야 합니다. 

Msg 3709, Level 16, State 2, Line 1
이 데이터베이스는 데이터베이스 스냅숏 "AdventureWorks_snapshot"에서 참조하므로 삭제할 수 없습니다. 먼저 이 데이터베이스를 삭제하십시오.


7. 마무리
스냅숏을 사용할 때 원본 데이터베이스 변경이 발생하면 변경 전 데이터를 스냅숏 데이터베이스 복사해야 하므로 추가 I/O가 발생하게 됩니다. 변경이 잦아지면 I/O 성능 저하가 발생할 수 있다는 것, 그리고 백업을 대신해서 사용하면 안된다는 것은 반드시 명심해야겠습니다. 


[참고자료]
How to: Create a Database Snapshot (Transact-SQL)


작성자 : Lai Go / 작성일자 : 2009.10.01
Posted by Lai Go