Database snapshot

Database Snapshot 是数据库某一个时间点的一个read-only副本，这个read-only副本占用的存储空间不一定很大。如果Source DB的某一个page修改了，那么在这个Page被修改之前，数据库会先copy一份数据，存放在Database Snapshot 的数据库Files中，即保留这个page的copy。SourceDB中的数据会修改，但是Database Snapshot的数据不会修改。

The actual space needed for each snapshot is typically much less than the space required for the original database because the snapshot stores only pages that have changed.

1， 创建database snapshot必须使用 TSQL clause

Syntax

--Create a database snapshot
CREATE DATABASE database_snapshot_name 
    ON 
    (
        NAME = logical_file_name,
        FILENAME = ‘os_file_name‘ 
    ) [ ,...n ] 
    AS SNAPSHOT OF source_database_name
[;]

logical_file_name是 source_database_name的file，filename指定snapshot文件存储的物理文件。

CREATE DATABASE [db_study_snapshot]
 CONTAINMENT = NONE
 ON 
( 
    NAME = N‘db_study‘, 
    FILENAME = N‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\db_study.mdf‘ 
) AS SNAPSHOT OF [db_study]
GO

2，database snapshot 就是一个special db，可以在snapshot中查询，但是不能修改数据

use [db_study_snapshot]

select *
from dbo.dt_test

3，更新数据

database snapshot  是source db某一个时间点的一个read-only copy，如果source db的数据发生变化，这种数据变化不会影响在database snapshot中的数据，但是，sql server 在更新数据之前，首先会将page的数据文件复制到database snapshot的files中，即保留这个page的原始副本，然后更新source db的data page。

The snapshot files contain only the data that has changed from the source. Every time a page in the source is update, sql server checks wheter the page has already been copied to snapshot files and if it hasn‘t ,it‘s copied at that time. This operation is called a copy-on-write operation.

4，读取数据

在snapshot中读取数据，如果page存在于snapshot的files中，那么从snapshot的files中读取数据；如果page不存在于snapshot的files，说明，这部分数据没有更新，snapshot和source db公用一份db files。

when a process reads from the snapshot, it first check wheter the page it want is in the snapshot files or still on the source db files. If the page is in the snapshot files, the process reads from the snapshot files;If the page is in sill on the source db files, the process reads from the source db files.

5, read with nolock

使用snapshot最大的好处是读取数据不会加锁。

When a process reads from a database snapshot, no locks are taken no matter what isolation level you are in. It is true wheter the page is read from the snapshot files or from the source db files.

 6，删除 database snapshot

 snapshot is a special database

drop database database_snapshot_name

Database snapshot

原文：http://www.cnblogs.com/ljhdo/p/4972974.html