SQL Server数据库快照

介绍 (Introduction)

MS SQL Server 2005 and later versions include the Database Snapshot feature to have snapshot of the database for reports, as a copy in different periods.

MS SQL Server 2005和更高版本包含“数据库快照”功能，以具有用于报告的数据库快照，作为不同时期的副本。

The Database Snapshot can be created multiple times and it can only be created using the T-SQL.

数据库快照可以创建多次，并且只能使用T-SQL创建。

In this article, we will show how to create a Database Snapshot, how to see the snapshot created in the SQL Server Management Studio (SSMS), how to recover objects dropped or data removed using the snapshot.

在本文中，我们将展示如何创建数据库快照，如何查看在SQL Server Management Studio（SSMS）中创建的快照，如何恢复使用快照删除的对象或删除的数据。

Finally, we will learn how to create Snapshots automatically for reporting purposes.

最后，我们将学习如何为报告目的自动创建快照。

要求 (Requirements)

• SQL Server Enterprise or Evaluation Edition is required
  需要SQL Server Enterprise或评估版
• We are using SQL Server 2014, but earlier versions can be used
  我们正在使用SQL Server 2014，但是可以使用早期版本
• The Adventureworks Database is required
  需要Adventureworks数据库
• The Adventurewoks database has to be online
  Adventurewoks数据库必须在线

入门 (Getting started)

To create a database snapshot, we need to use the T-SQL. It is the only way to do it. You cannot create a Database Snapshot in the SSMS.

要创建数据库快照，我们需要使用T-SQL。 这是唯一的方法。 您不能在SSMS中创建数据库快照。

The syntax is the following:

语法如下：

 
CREATE DATABASE AdventureWorks_snapshot ON
 
( NAME = AdventureWorks2012_Data, 
 
--Name of the snapshot file
 
FILENAME =
 
'C:\script2\AdventureWorks_data_1800.ss' )
 
--It is a Snapshot of the adventureworks2012 database
 
AS SNAPSHOT OF [AdventureWorks2012];
 
GO
 

As you can see, the syntax is similar to a normal database creation except for two things:

如您所见，该语法与普通数据库的创建相似，除了两点：

1. We use the word AS SNAPSHOT OF DATABASE_NAME to specify the name of the database that requires a snapshot.
   我们使用单词AS SNAPSHOT OF DATABASE_NAME来指定需要快照的数据库的名称。
2. By default, it is better to specify the extension of the snapshot datafile as .ss (which means SnapShot.
   默认情况下，最好将快照数据文件的扩展名指定为.ss（即SnapShot。

If everything is OK, you will be able to see the snapshot created in the SSMS:

如果一切正常，您将能够看到在SSMS中创建的快照：

Figure 1

图1

The snapshots have read-only tables. If you try to update or delete the data you will not be able to do it. You will receive a read only message:

快照具有只读表。 如果您尝试更新或删除数据，则将无法执行。 您将收