sql server快照复制mysql_数据库快照 (SQL Server)

数据库快照 (SQL Server)Database Snapshots (SQL Server)

08/08/2016

本文内容

适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions)适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions)

数据库快照是 SQL ServerSQL Server 数据库(源数据库)的只读静态视图。A database snapshot is a read-only, static view of a SQL ServerSQL Server database (the source database). 自创建快照那刻起，数据库快照在事务上与源数据库一致。The database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation. 数据库快照始终与其源数据库位于同一服务器实例上。A database snapshot always resides on the same server instance as its source database. 虽然数据库快照提供与创建快照时处于相同状态的数据的只读视图，但快照文件的大小随着对源数据库的更改而增大。While database snapshots provide a read-only view of the data in the same state as when the snapshot was created, the size of the snapshot file grows as changes are made to the source database. 有关详细信息，请参阅下面的功能概述部分。For details, see the Feature Overview section below.

给定源数据库中可以存在多个快照。Multiple snapshots can exist on a given source database. 在数据库所有者显式删除每个数据库快照之前，该快照将一直保留。Each database snapshot persists until it is explicitly dropped by the database owner.

备注

数据库快照与快照备份、事务的快照隔离或快照复制无关。Database snapshots are unrelated to snapshot backups, snapshot isolation of transactions, or snapshot replication.

本主题内容：In this Topic:

功能概述Feature Overview

数据库快照在数据页级运行。Database snapshots operate at the data-page level. 在第一次修改源数据库页之前，先将原始页从源数据库复制到快照。Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. 快照将存储原始页，保留它们在创建快照时的数据记录。The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. 对要进行第一次修改的每一页重复此过程。The same process is repeated for every page that is being modified for the first time. 对于用户而言，数据库快照似乎始终保持不变，因为对数据库快照的读操作始终访问原始数据页，而与页驻留的位置无关。To the user, a database snapshot appears never to change, because read operations on a database snapshot always access the original data pages, regardless of where they reside.

为了存储复制的原始页，快照使用一个或多个“稀疏文件” 。To store the copied original pages, the snapshot uses one or more sparse files. 最初，稀疏文件实质上是空文件，不包含用户数据并且未被分配存储用户数据的磁盘空间。Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. 随着源数据库中更新的页越来越多，文件的大小也不断增长。As more and more pages are updated in the source database, the size of the file grows. 下图说明了两种相对的更新模式对快照大小的影响。The following figure illustrates the effects of two contrasting update patterns on the size of a snapshot. 更新模式 A 反映的是在快照使用期限内仅有 30% 的原始页更新的环境。Update pattern A reflects an environment in which only 30 percent of the original pages are updated during the life of the snapshot. 更新模式 B 反映的是在快照使用期限内有 80% 的原始页更新的环境。Update pattern B reflects an environment in which 80 percent of the original pages are updated during the life of the snapshot.

数据库快照的优点Benefits of Database Snapshots

快照可用于报告目的。Snapshots can be used for reporting purposes.

客户端可以查询数据库快照，这对于基于创建快照时的数据编写报表是很有用的。Clients can query a database snapshot, which makes it useful for writing reports based on the data at the time of snapshot creation.

维护历史数据以生成报表。Maintaining historical data for report generation.

快照可以从特定时点扩展用户对数据的访问权限。A snapshot can extend user access to data from a particular point in time. 例如，您可以在给定时间段(例如，财务季度)要结束的时候创建数据库快照以便日后制作报表。For example, you can create a database snapshot at the end of a given time period (such as a financial quarter) for later reporting. 然后便可以在快照上运行期间要结束时创建的报表。You can then run end-of-period reports on the snapshot. 如果磁盘空间允许，还可以维护任意多个不同期间要结束时的快照，以便能够对这些时间段的结果进行查询。例如，调查单位性能。If disk space permits, you can also maintain end-of-period snapshots indefinitely, allowing queries against the results from these periods; for example, to investigate organizational performance.

使用为了实现可用性目标而维护的镜像数据库来减轻报表负载。Using a mirror database that you are maintaining for availability purposes to offload reporting.

使用带有数据库镜像的数据库快照，使您能够访问镜像服务器上的数据以生成报表。Using database snapshots with database mirroring permits you to make the data on the mirror server accessible for reporting. 而且，在镜像数据库上运行查询可以释放主体数据库上的资源。Additionally, running queries on the mirror database can free up resources on the principal.

使数据免受管理失误所带来的影响。Safeguarding data against administrative error.

如果源数据库上出现用户错误，您可将源数据库恢复到创建给定数据库快照时的状态。In the event of a user error on a source database, you can revert the source database to the state it was in when a given database snapshot was created. 丢失的数据仅限于创建快照后数据库更新的数据。Data loss is confined to updates to the database since the snapshot's creation.

例如，在进行重大更新(比如大容量更新或架构更改)前，对数据库创建数据库快照以保护数据。For example, before doing major updates, such as a bulk update or a schema change, create a database snapshot on the database protects data. 一旦进行了错误操作，可以使用快照将数据库恢复到生成快照时的状态。If you make a mistake, you can use the snapshot to recover by reverting the database to the snapshot. 为此目的进行的恢复很可能比从备份还原快得多；但是，此后您无法对数据进行前滚操作。Reverting is potentially much faster for this purpose than restoring from a backup; however, you cannot roll forward afterward.

重要

无法对脱机或损坏的数据库进行恢复。Reverting does not work in an offline or corrupted database. 因此，为了保护数据库，非常有必要定期执行备份并测试还原计划。Therefore, taking regular backups and testing your restore plan are necessary to protect a database.

备注

数据库快照与源数据库相关。Database snapshots are dependent on the source database. 因此，使用数据库快照恢复数据库不能代替备份和还原策略。Therefore, using database snapshots for reverting a database is not a substitute for your backup and restore strategy. 严格按计划执行备份仍然至关重要。Performing all your scheduled backups remains essential. 如果必须将源数据库还原到创建数据库快照的时间点，请实施允许您执行该操作的备份策略。If you must restore the source database to the point in time at which you created a database snapshot, implement a backup policy that enables you to do that.

使数据免受用户失误所带来的影响。Safeguarding data against user error.

定期创建数据库快照，可以减轻重大用户错误(例如，删除的表)的影响。By creating database snapshots on a regular basis, you can mitigate the impact of a major user error, such as a dropped table. 为了很好地保护数据，可以创建时间跨度足以识别和处理大多数用户错误的一系列数据库快照。For a high level of protection, you can create a series of database snapshots spanning enough time to recognize and respond to most user errors. 例如，根据磁盘资源，可以每 24 小时创建 6 到 12 个滚动快照。For instance, you might maintain 6 to 12 rolling snapshots spanning a 24-hour interval, depending on your disk resources. 每创建一个新的快照，就删除最早的快照。Then, each time a new snapshot is created, the earliest snapshot can be deleted.

若要从用户错误中恢复，可以将数据库恢复到在错误发生的前一时刻的快照。To recover from a user error, you can revert the database to the snapshot immediately before the error. 为此目的进行的恢复很可能比从备份还原快得多；但是，此后您无法对数据进行前滚操作。Reverting is potentially much faster for this purpose than restoring from a backup; however, you cannot roll forward afterward.

或者，也可以利用快照中的信息，手动重新创建删除的表或其他丢失的数据。Alternatively, you may be able to manually reconstruct a dropped table or other lost data from the information in a snapshot. 例如，可以将快照中的数据大容量复制到数据库中，然后手动将数据合并回数据库中。For instance, you could bulk copy the data out of the snapshot into the database and manually merge the data back into the database.

备注

使用数据库快照的原因，决定了数据库需要多少个并发快照、多久创建一次新快照以及将其保留多久。Your reasons for using database snapshots determine how many concurrent snapshots you need on a database, how frequently to create a new snapshot, and how long to keep it.

管理测试数据库Managing a test database

在测试环境中，当每一轮测试开始时针对要包含相同数据的数据库重复运行测试协议将十分有用。In a testing environment, it can be useful when repeatedly running a test protocol for the database to contain identical data at the start of each round of testing. 在运行第一轮测试前，应用程序开发人员或测试人员可以在测试数据库中创建数据库快照。Before running the first round, an application developer or tester can create a database snapshot on the test database. 每次运行测试之后，数据库都可以通过恢复数据库快照快速返回到它以前的状态。After each test run, the database can be quickly returned to its prior state by reverting the database snapshot.

术语和定义Terms and Definitions

database snapshotdatabase snapshot

一个数据库(源数据库)的事务一致的只读静态视图。A transactionally consistent, read-only, static view of a database (the source database).

源数据库 (source database)source database

对于数据库快照，指的是在其上创建快照的数据库。For a database snapshot, the database on which the snapshot was created. 数据库快照与源数据库相关。Database snapshots are dependent on the source database. 数据库快照必须与数据库在同一服务器实例上。The snapshots of a database must be on the same server instance as the database. 此外，如果数据库因某种原因而不可用，则它的所有数据库快照也将不可用。Furthermore, if that database becomes unavailable for any reason, all of its database snapshots also become unavailable.

稀疏文件 (sparse file)sparse file

NTFS 文件系统提供的文件，需要的磁盘空间要比其他文件格式少很多。A file provided by the NTFS file system that requires much less disk space than would otherwise be needed. 稀疏文件用于存储复制到数据库快照的页面。A sparse file is used to store pages copied to a database snapshot. 首次创建稀疏文件时，稀疏文件占用的磁盘空间非常少。When first created, a sparse file takes up little disk space. 随着数据写入数据库快照，NTFS 会将磁盘空间逐渐分配给相应的稀疏文件。As data is written to a database snapshot, NTFS allocates disk space gradually to the corresponding sparse file.

数据库快照的先决条件和限制Prerequisites for and Limitations on Database Snapshots

本节内容：In This Section:

先决条件Prerequisites

可以使用任何恢复模式的源数据库必须满足以下先决条件：The source database, which can use any recovery model, must meet the following prerequisites:

服务器实例必须在支持数据库快照的 SQL ServerSQL Server 版本上运行。The server instance must be running on an edition of SQL ServerSQL Server that supports database snapshots.

源数据库必须处于联机状态，除非该数据库是数据库镜像会话中的镜像数据库。The source database must be online, unless the database is a mirror database within a database mirroring session.

您可在可用性组中的任何主数据库或辅助数据库上创建数据库快照。You can create a database snapshot on any primary or secondary database in an availability group. 副本角色必须是 PRIMARY 或 SECONDARY，且不处于 RESOLVING 状态。The replica role must be either PRIMARY or SECONDARY, not in the RESOLVING state.

当您创建一个数据库快照时，我们建议数据库同步状态是 SYNCHRONIZING 或 SYNCHRONIZED。We recommend that the database synchronization state be SYNCHRONIZING or SYNCHRONIZED when you create a database snapshot. 但是，当数据库同步状态为 NOT SYNCHRONIZING 时，可以创建数据库快照。However, database snapshots can be created when the database synchronization state is NOT SYNCHRONIZING.

若要在镜像数据库中创建数据库快照，数据库必须处于 SYNCHRONIZED 镜像状态。To create a database snapshot on a mirror database, the database must be in the SYNCHRONIZED mirroring state.

不能将源数据库配置为可缩放共享数据库。The source database cannot be configured as a scalable shared database.

源数据库不得包含 MEMORY_OPTIMIZED_DATA 文件组。The source database must not contain a MEMORY_OPTIMIZED_DATA filegroup.

备注

所有恢复模式都支持数据库快照。All recovery models support database snapshots.

源数据库的限制Limitations on the Source Database

只要存在数据库快照，快照的源数据库就存在以下限制：As long as a database snapshot exists, the following limitations exist on the snapshot's source database:

不能对数据库进行删除、分离或还原。The database cannot be dropped, detached, or restored.

备注

可以备份源数据库，这方面将不受数据库快照的影响。Backing up the source database works normally; it is unaffected by database snapshots.

源数据库的性能受到影响。由于每次更新页时都会对快照执行“写入时复制”操作，导致源数据库上的 I/O 增加。Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.

不能从源数据库或任何快照中删除文件。Files cannot be dropped from the source database or from any snapshots.

数据库快照的限制Limitations on Database Snapshots

数据库快照存在以下限制：The following limitations apply to database snapshots:

数据库快照必须与源数据库在相同的服务器实例上创建和保留。A database snapshot must be created and remain on the same server instance as the source database.

始终对整个数据库制作数据库快照。Database snapshots always work on an entire database.

数据库快照依赖于源数据库，但不是冗余存储。Database snapshots are dependent on the source database and are not redundant storage. 它们无法防止磁盘错误或其他类型的损坏。They do not protect against disk errors or other types of corruption. 因此，使用数据库快照恢复数据库不能代替备份和还原策略。Therefore, using database snapshots for reverting a database is not a substitute for your backup and restore strategy. 严格按计划执行备份仍然至关重要。Performing all your scheduled backups remains essential. 如果必须将源数据库还原到创建数据库快照的时间点，请实施允许您执行该操作的备份策略。If you must restore the source database to the point in time at which you created a database snapshot, implement a backup policy that enables you to do that.

当将源数据库中更新的页强制压入快照时，如果快照用尽磁盘空间或者遇到其他错误，则该快照将成为可疑快照并且必须将其删除。When a page getting updated on the source database is pushed to a snapshot, if the snapshot runs out of disk space or encounters some other error, the snapshot becomes suspect and must be deleted.

快照为只读。Snapshots are read-only. 由于它们为只读，所以无法升级。Since they are read only, they cannot be upgraded. 因此，可以知道数据库快照在升级后会不可用。Therefore, database snapshots are not expected to be viable after an upgrade.

禁止对 model数据库、 master数据库和 tempdb 数据库创建快照。Snapshots of the model, master, and tempdb databases are prohibited.

不能更改数据库快照文件的任何规范。You cannot change any of the specifications of the database snapshot files.

不能从数据库快照中删除文件。You cannot drop files from a database snapshot.

不能备份或还原数据库快照。You cannot back up or restore database snapshots.

不能附加或分离数据库快照。You cannot attach or detach database snapshots.

不能在 FAT32 文件系统或 RAW 分区上创建数据库快照。You cannot create database snapshots on FAT32 file system or RAW partitions. 数据库快照所用的稀疏文件由 NTFS 文件系统提供。The sparse files used by database snapshots are provided by the NTFS file system.

数据库快照不支持全文索引。Full-text indexing is not supported on database snapshots. 不从源数据库传播全文目录。Full-text catalogs are not propagated from the source database.

数据库快照将继承快照创建时其源数据库的安全约束。A database snapshot inherits the security constraints of its source database at the time of snapshot creation. 由于快照是只读的，因此无法更改继承的权限，对源数据库的更改权限将不反映在现有快照中。Because snapshots are read-only, inherited permissions cannot be changed and permission changes made to the source will not be reflected in existing snapshots.

快照始终反映创建该快照时的文件组状态：联机文件组将保持联机状态，脱机文件组将保持脱机状态。A snapshot always reflects the state of filegroups at the time of snapshot creation: online filegroups remain online, and offline filegroups remain offline. 有关详细信息，请参阅本主题后面的“含有脱机文件组的数据库快照”。For more information, see "Database Snapshots with Offline Filegroups" later in this topic.

如果源数据库的状态为 RECOVERY_PENDING，可能无法访问其数据库快照。If a source database becomes RECOVERY_PENDING, its database snapshots may become inaccessible. 但是，当解决了源数据库的问题之后，快照将再次变成可用快照。After the issue on the source database is resolved, however, its snapshots should become available again.

数据库中的任何 NTFS 只读文件或 NTFS 压缩文件不支持恢复。Reverting is unsupported for any NTFS read-only or NTFS compressed files in the database. 尝试恢复包含下列任意一种文件组的数据库将失败。Attempts to revert a database containing either of these types of filegroups will fail.

在日志传送配置中，只能针对主数据库，而不能针对辅助数据库创建数据库快照。In a log shipping configuration, database snapshots can be created only on the primary database, not on a secondary database. 如果您在主服务器实例和辅助服务器实例之间切换角色，则在将主数据库设置为辅助数据库之前，必须先删除所有数据库快照。If you switch roles between the primary server instance and a secondary server instance, you must drop all the database snapshots before you can set the primary database up as a secondary database.

不能将数据库快照配置为可缩放共享数据库。A database snapshot cannot be configured as a scalable shared database.

数据库快照不支持 FILESTREAM 文件组。FILESTREAM filegroups are not supported by database snapshots. 如果源数据库中存在 FILESTREAM 文件组，则它们在数据库快照中被标识为脱机状态，且其数据库快照不能用于恢复数据库。If FILESTREAM filegroups exist in a source database, they are marked as offline in its database snapshots, and the database snapshots cannot be used for reverting the database.

备注

对数据库快照执行的 SELECT 语句不能指定 FILESTREAM 列；否则，将返回如下错误消息： Could not continue scan with NOLOCK due to data movement.A SELECT statement that is executed on a database snapshot must not specify a FILESTREAM column; otherwise, the following error message will be returned: Could not continue scan with NOLOCK due to data movement.

当有关只读快照的统计信息丢失或变得陈旧时， 数据库引擎Database Engine 将创建临时统计信息并在 tempdb 中进行维护。When statistics on a read-only snapshot are missing or stale, the 数据库引擎Database Engine creates and maintains temporary statistics in tempdb. 有关详细信息，请参阅统计信息。For more information, see Statistics.

磁盘空间要求Disk Space Requirements

数据库快照占用磁盘空间。Database snapshots consume disk space. 如果数据库快照用尽了磁盘空间，将被标记为可疑，必须将其删除。If a database snapshot runs out of disk space, it is marked as suspect and must be dropped. (但是，源数据库不会受到影响，对其执行的操作仍能继续正常进行。)然而，与一份完整的数据库相比，快照具有高度空间有效性。(The source database, however, is not affected; actions on it continue normally.) Compared to a full copy of a database, however, snapshots are highly space efficient. 快照仅需足够存储空间来存储在其生存期中更改的页。A snapshot requires only enough storage for the pages that change during its lifetime. 通常情况下，快照只会保留一段有限的时间，因此其大小不是主要问题。Generally, snapshots are kept for a limited time, so their size is not a major concern.

但是，保留快照的时间越长，越有可能将可用空间用完。The longer you keep a snapshot, however, the more likely it is to use up available space. 稀疏文件最大只能增长到创建快照时相应的源数据库文件的大小。The maximum size to which a sparse file can grow is the size of the corresponding source database file at the time of the snapshot creation. 如果数据库快照用完了磁盘空间，则必须删除该快照。If a database snapshot runs out of disk space, it must be deleted (dropped).

备注

除文件空间外，数据库快照与数据库占用的资源量大致相同。Except for file space, a database snapshot consumes roughly as many resources as a database.

含有脱机文件组的数据库快照Database Snapshots with Offline Filegroups

当您尝试执行下列任何操作时，源数据库中的脱机文件组都将影响数据库快照：Offline filegroups in the source database affect database snapshots when you try to do any of the following:

创建快照Create a snapshot

当源数据库具有一个或多个脱机文件组时，快照创建只有在文件组处于脱机状态时才能成功。When a source database has one or more offline filegroups, snapshot creation succeeds with the filegroups offline. 不能为脱机文件组创建稀疏文件。Sparse files are not created for the offline filegroups.

使文件组脱机Take a filegroup offline

可以在源数据库中使文件脱机。You can take a file offline in the source database. 但是，如果创建快照时文件组处于联机状态，则该文件组在数据库快照中仍将保持联机状态。However, the filegroup remains online in database snapshots if it was online when the snapshot was created. 如果查询的数据在快照创建后已更改，则在快照中可以访问原始数据页。If the queried data has changed since snapshot creation, the original data page will be accessible in the snapshot. 但是，使用快照访问文件组中未修改数据的查询可能会由于出现输入/输出 (I/O) 错误而失败。However, queries that use the snapshot to access unmodified data in the filegroup are likely to fail with input/output (I/O) errors.

使文件组联机Bring a filegroup online

只要数据库具有任何快照，就不能使其中的文件组联机。You cannot bring a filegroup online in a database that has any database snapshots. 如果在创建快照时文件组处于脱机状态，或当数据库快照存在时使文件组脱机，则文件组将保持脱机状态。If a filegroup is offline at the time of snapshot creation or is taken offline while a database snapshot exists, the filegroup remains offline. 这是因为使文件重新联机需要还原该文件，而如果数据库已具有快照，则无法执行此操作。This is because bringing a file back online involves restoring it, which is not possible if a database snapshot exists on the database.

将源数据库恢复到快照Revert the source database to the snapshot

将源数据库恢复到数据库快照要求除创建快照时处于脱机状态的文件组外，所有文件组都要处于联机状态。Reverting a source database to a database snapshot requires that all of the filegroups are online except for filegroups that were offline when the snapshot was created.

相关任务Related Tasks

另请参阅See Also