SQL Server 스냅숏(snapshot)이란 특정 시점의 변경 불가능한 읽기 전용 데이터베이스를 의미합니다. AdventureWorks 데이터베이스를 대상으로 스냅숏을 생성하고 테스트를 통해 어떻게 동작하는지 간단히 살펴 보고자 아래와 같이 정리하였습니다.
USE AdventureWorks_snapshot
SELECT * FROM Person.Address WHERE AddressID < 10
[환경]
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
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
