Database snapshot is a great feature that offers virtual read only consistent database copy. When we create the database snapshot in the live operational database, it takes a database point in time static view and Rollback all uncommitted transactions in the snapshot database so we will not be having any inconsistent data that is yet to be committed. Database snapshot always exists on the Source database server.

数据库快照是一项出色的功能，可提供虚拟只读一致的数据库副本。 当我们在实时运行数据库中创建数据库快照时，它需要一个数据库时间点静态视图并回滚快照数据库中所有未提交的事务，因此我们不会有任何尚未提交的不一致数据。 数据库快照始终存在于源数据库服务器上。

Database snapshot works on the pages (the fundamental unit of data storage in SQL server is the page). The disk space allocated to a data file in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level. That is, SQL server reads or writes whole data pages.

数据库快照适用于页面（SQL Server中数据存储的基本单位是页面）。 分配给数据库中数据文件的磁盘空间在逻辑上分为从0到n连续编号的页。 磁盘I / O操作在页面级别执行。 也就是说，SQL Server读取或写入整个数据页。

Basically, it creates a sparse file and will be pointing to the original databases, written only in case of any insert, update and delete statement is in the source database.

基本上，它将创建一个稀疏文件并指向原始数据库，仅在源数据库中存在任何insert，update和delete语句的情况下才编写该文件。

Suppose there is no operation going into the server when we created a database snapshot so snapshot will look like

假设我们创建数据库快照时没有任何操作进入服务器，所以快照看起来像

源数据库 ( Source Database )

快照数据库（稀疏文件） ( Snapshot Database (Sparse File) )

So we have the blank copy of the database here. Now, if any Read Operation occurs ,it will be pointing to the original database only as no page modification is done since snapshot creation.

因此，这里有数据库的空白副本。 现在，如果发生任何读取操作，它将仅指向原始数据库，因为自创建快照以来未进行任何页面修改。

Now suppose due to a DML operation (insert\update\delete) Page 2, 5, and 8 has been modified and user operation requires page 1,2, 4 and page 8 so below picture shows how the snapshot will be and how the read operation will be performed.

现在假设由于DML操作（插入\更新\删除）的第2、5和8页已被修改，并且用户操作需要第1,2、4和8页，因此下图显示了快照的方式以及如何读取快照操作将被执行。

We can see here whichever page gets changed due to any operation, its original page (before modification) is copied into the sparse file (snapshot), so if user operation request to read the snapshot database it works in below ways

我们可以在这里看到任何由于任何操作而发生更改的页面，其原始页面（修改前）将被复制到稀疏文件（快照）中，因此，如果用户操作请求读取快照数据库，它将以以下方式工作

1. If a page is modified, read operation occurs on sparse File
   如果页面被修改，则对稀疏文件进行读取操作
2. If the page is not modified it still points to the source database page
   如果页面未修改，它仍指向源数据库页面

This is the concept behind the snapshot, thus, its size is small compared to the original database but it all depends on the operation if there are too many operations in the database which gets page modified so the snapshot size will be kept increasing.

这是快照背后的概念，因此，与原始数据库相比，快照的大小较小，但是如果数据库中有太多需要修改页面的操作，则这完全取决于操作，因此快照大小将不断增加。

So whenever we create the snapshot following operation will be done

因此，无论何时创建快照，都将执行以下操作

1. 1. It creates an empty file, i.e. sparse file for each source database data file
   1.它为每个源数据库数据文件创建一个空文件，即稀疏文件
2. Uncommitted transactions are rolled back, thus having a consistent copy of the database
   未提交的事务将回滚，从而具有一致的数据库副本
3. All dirty pages will be returned to the disk
   所有脏页将返回到磁盘
4. The user can query the snapshot database now
   用户现在可以查询快照数据库

Now let’s say there are many database pages being updated so now the snapshot will look like

现在，假设有许多数据库页面正在更新，所以现在快照看起来像

Here we can see the as there are many operations being performed all pages shown above are modified, thus the snapshot size will be increased. We should always keep an eye on the growth of the snapshot database.

在这里我们可以看到，由于正在执行许多操作，因此上面显示的所有页面均被修改，因此快照大小将增加。 我们应该时刻关注快照数据库的增长。

The best use of Database snapshot can be during production changes\ upgrades\releases. Normally, database backup is taken and if the database size is big it is really time and space consuming since we will have to keep the backup copy apart from our regular backups. So instead of backup we can create the Database snapshot which is really quick and can perform the release, and once we have verified the changes are good we can easily drop the snapshot and also in some cases we require to see the values before changes we can view that as well.

最好在生产更改\升级\发行期间使用数据库快照。 通常，进行数据库备份，如果数据库很大，则确实会浪费时间和空间，因为我们必须将备份副本与常规备份分开。 因此，代替备份，我们可以创建一个非常快的数据库快照，并且可以执行发布，并且一旦我们确认更改是好的，我们就可以轻松删除快照，并且在某些情况下，我们需要在更改之前先查看值对此也是如此。

数据库快照和高可用性 ( Database snapshot and High availability )

Database snapshot works well with the log shipping and database mirroring too. Normally log shipping databases are maintained in the no recovery model means no database connections can be made and it will be useful only when failover is made.

数据库快照也可以与日志传送和数据库镜像一起很好地工作。 通常，日志传送数据库以无恢复模式维护，这意味着无法建立数据库连接，并且仅在进行故障转移时才有用。

We can create a database snapshot for log shipping secondary database and use the database to run the queries.

我们可以为日志传送辅助数据库创建数据库快照，并使用该数据库运行查询。

In database mirroring, mirror database is always in no recovery mode, whether it is synchronous and asynchronous mode. By creating snapshots of the mirror database, we can easily query the mirror database so it is quite a useful feature to offload and use it for reporting purposes.

在数据库镜像中，无论是同步模式还是异步模式，镜像数据库始终处于无恢复模式。 通过创建镜像数据库的快照，我们可以轻松地查询镜像数据库，因此，将其卸载并用于报告目的是一项非常有用的功能。

But there is some overhead if we are using synchronous mirror mode. As in synchronous mode, transactions have to be committed on both Principal and mirror servers before marking it complete so for a snapshot as it requires original page, it will add an extra task as copy database page from the mirror database to mirror Snapshot.

但是，如果我们使用同步镜像模式，则会有一些开销。 与同步模式一样，在将事务标记为完成之前，必须在主体服务器和镜像服务器上都提交事务，因此对于快照（因为它需要原始页面），它将添加一个额外的任务，即从镜像数据库复制数据库页面以镜像快照。

数据库快照的好处 ( Benefits of Database snapshot )

1. A database snapshot is convenient, reliable, Read- only, point-in-time copy of the database.
   数据库快照是数据库的便捷，可靠，只读的时间点副本。
2. It is very beneficial when doing critical updates to the database as if anything goes wrong database can be restored to that point without any hassle.
   在对数据库进行关键更新时，这非常有益，因为如果发生任何错误，可以毫无问题地将数据库还原到该点。
3. If we want to view database suppose at 7 pm, we can create database snapshot and easily query the database, however, without snapshot we have the option to take the database backup and restore using some different name but it requires considerable system resources especially disk space.
   如果要在晚上7点查看数据库，我们可以创建数据库快照并轻松查询数据库，但是，如果没有快照，我们可以选择使用其他名称进行数据库备份和还原，但是它需要大量的系统资源，尤其是磁盘空间。
4. Sparse file (snapshot) is small and easy to create however the size of snapshot depends upon the amount of operation and page updated.
   稀疏文件（快照）很小且易于创建，但是快照的大小取决于操作量和更新的页面。
5. Multiple database snapshots are possible for a single database.
   一个数据库可以有多个数据库快照。
6. There is no dependency for the recovery model, it works with full, bulk-logged and simple also.
   恢复模型没有依赖性，它可以与完整，批量记录和简单一起使用。
7. Snapshot creation is really quick, it takes a few seconds only for big databases too.
   快照创建非常快，对于大型数据库也只需几秒钟。

数据库快照的局限性和缺点 ( Limitations and cons of Database snapshot )

1. A database snapshot can be created onto the source server only. It cannot be moved to another server
   只能将数据库快照创建到源服务器上。 无法将其移动到另一台服务器
2. We can’t drop an Original Source Database as long as a referring snapshot exists in that database
   只要引用快照存在于原始数据库中，我们就无法删除它
3. It cannot be Backed up and also detach \attach doesn’t work with database snapshots
   它无法备份，也无法分离\ attach对数据库快照不起作用
4. Snapshots can be created for only user databases, not for the master, model and msdb
   只能为用户数据库创建快照，而不能为master，model和msdb创建快照
5. If we revert to a snapshot log Chain will get broken, thus we have to take a full or differential backup to bridge the log chain sequence
   如果我们还原到快照日志，则链会损坏，因此我们必须进行完整备份或差异备份来桥接日志链序列
6. Both the source DB and the snapshot will be unavailable when the actual reversion process is in progress
   实际的还原过程正在进行时，源数据库和快照都将不可用
7. If the source database is unavailable or corrupted we cannot use the database snapshot to revert it to the original state
   如果源数据库不可用或已损坏，我们将无法使用数据库快照将其还原为原始状态
8. We cannot add new users for database since it is a read-only copy
   我们无法为数据库添加新用户，因为它是只读副本
9. There is no graphical user interface for creating and reverting back the snapshot, this need to be done from query only
   没有用于创建和还原快照的图形用户界面，这仅需要通过查询来完成
10. There is some overhead for every DML operation as before operation page needs to be moved out
    每个DML操作都有一些开销，因为操作页面需要移出之前
11. If the drive in which snapshot exists is out of space which causes any DML to fail the snapshot will be in suspect mode and non-recoverable
    如果存在快照的驱动器空间不足，导致任何DML失败，则快照将处于可疑模式，并且不可恢复
12. The full-text index is not available with a snapshot
    全文索引不适用于快照
13. It is available with enterprise edition only
    它仅适用于企业版
14. Database files that were online during snapshot creation should be online during the snapshot revert also
    在快照创建过程中处于联机状态的数据库文件在快照还原期间也应处于联机状态

不建议使用数据库快照的条件 ( Conditions where Database snapshot is not recommended )

1. It does not substitute for high availability solution such as mirroring, log shipping etc.
   它不能替代高可用性解决方案，例如镜像，日志传送等。
2. Very high user based activity as it might have extra overhead
   基于用户的活动非常频繁，因为这可能会产生额外的开销
3. Replacement of database backup as it is fully dependent on the source database
   替换数据库备份，因为它完全取决于源数据库

翻译自: https://www.sqlshack.com/database-snapshot-in-sql-server/