被误删的数据,如何用SQL快照恢复

什么是快照

数据库快照是sql server 2005的一个新功能。MSDN上对它的定义是：

数据库快照是数据库(称为“源数据库”)的只读静态视图。在创建时，每个数据库快照在事务上都与源数据库一致。在创建数据库快照时，源数据库通常会有打开的事务。在快照可以使用之前，打开的事务会回滚以使数据库快照在事务上取得一致。

创建示例数据库

1.  Use MASTER; 
2.  GO 
3.  CREATE DATABASE [Snapshot_Test] ON  PRIMARY 
4.  ( NAME = N'Snapshot_Test', --数据库名称 
5.  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Snapshot_Test.mdf', 
6.  --数据文件存放位置及数据文件名称 
7.  SIZE = 3072KB , 
8.  --初始容量 
9.  MAXSIZE = UNLIMITED, 
10.  --最大容量 
11.  FILEGROWTH = 1024KB 
12.  --增长容量 
13.  ) 
14.  LOG ON  
15.  ( NAME = N'Snapshot_Test_log', 
16.  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Snapshot_Test_log.ldf' , 
17.  SIZE = 504KB , 
18.  MAXSIZE = UNLIMITED, 
19.  FILEGROWTH = 10%) 
20.  COLLATE Chinese_PRC_CI_AS 
21.  GO 
22.  EXEC dbo.sp_dbcmptlevel @dbname=N'Snapshot_Test', @new_cmptlevel=130 
23.  GO 
24.  USE [Snapshot_Test] 
25.  GO 
26.  SET ANSI_NULLS ON 
27.  GO 
28.  SET QUOTED_IDENTIFIER ON 
29.  GO 
30.  CREATE TABLE [dbo].[test]( 
31.  [id] [int] NOT NULL, 
32.  [name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL 
33.  ) ON [PRIMARY] 
34.  GO 

(提示：可以左右滑动代码)

也可以直接在SSMS里通过图形化界面创建测试数据库，这里不作赘述。在创建完数据库之后，我们又新建了一个test表，我们先往里面插入测试数据。如下：

1.  use snapshot_Test; 
2.  go 
3.  INSERT INTO TEST (id,name)values(1,'hello 1'); 
4.  INSERT INTO TEST (id,name)values(2,'hello 2'); 
5.  INSERT INTO TEST (id,name)values(3,'hello 3'); 
6.  INSERT INTO TEST (id,name)values(4,'hello 4'); 
7.  GO 
8.  SELECT * FROM dbo.test; 

结果：

然后我们马上创建一个快照，创建快照使用的也是CREATE DATABASE语句，如下：

创建数据库快照

执行下面的创建快照的语句

1.  create database Snapshot_Test_shot ON 
2.  ( 
3.  --是源数据库的逻辑名 
4.  Name = Snapshot_Test, 
5.  --快照文件地址 
6.  FileName = 'D:\SqlData\Snapshot_Test_shot.ss' 
7.  ) 
8.  AS SNAPSHOT OF Snapshot_Test; 

结果：

现在的快照应该和我们新建的数据库一摸一样，可以通过sql server的对象浏览器查看数据库快照，我们可以通过USE [快照库名]来查询数据库快照

1.  use Snapshot_Test_shot; 
2.  go 
3.  SELECT * FROM dbo.test; 

结果：

删除测试库数据

下面我们删除测试数据库中的数据

1.  use Snapshot_Test; 
2.  go 
3.  DELETE FROM dbo.test; 

结果：

使用快照恢复数据库

上面删除是我们模拟误删了数据库中的数据，但是幸好我们做了数据库的快照。可以通过快照来还原刚才被误删的数据。

1.  --语法 
2.  RESTORE DATABASE DB_NAME 
3.  FROM DATABASE_SNAPSHOT = 'DB_SNAPSHOT_NAME' 

我们按照上面的语法进行恢复

1.  RESTORE DATABASE Snapshot_test 
2.  FROM DATABASE_SNAPSHOT = 'Snapshot_test_shot' 

执行完上面的恢复语句后，我们再查询一下测试库中的表

1.  use Snapshot_Test; 
2.  go 
3.  SELECT * FROM test 

结果：

数据又回来，证明可以通过创建快照来恢复被误删的数据。

注意：

1、快照是只读的，一旦创建不能修改

2、快照只能恢复数据库，不能恢复指定的表

3、为防止误删除，必须在数据插入或更新到数据库之后再创建快照，否则在插入或更新之前创建快照，插入或更新的数据被删除是无法恢复的。

4、快照一般与定时任务一起使用，可以定时创建不同的快照防止数据丢失。

删除数据库快照

和删除数据库语法一样

1.  DROP DATABASE Snapshot_Test_shot 

结果：

数据库快照下面没有任何快照了，说明已经被删除。

应用场景

在MSDN中数据库快照的典型应用是

1. 维护历史数据以生成报表。

2. 使用为了实现可用性目标而维护的镜像数据库来卸载报表。

3. 使数据免受管理失误所带来的影响。

4. 使数据免受用户失误所带来的影响。

如果觉得本文对你有帮助，可以转发关注支持一下