SQL如何使用快照恢复之前的数据

什么是快照

数据库快照是SQL server 2005的一个新功能。给出的定义如下

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

reg：

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

View Code

也可以直接在SSMS里通过图形化界面创建测试数据库，在创建完数据库后，又新建了一个test表

reg：

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

结果：

id

name

1

1

hello 1

2

2

hello 2

3

3

hello 3

4

4

hello 4

现在做测试。先做个快照，语法也是CREATE DATABASEY。

reg：

1create database Snapshot_Test_shot ON
2
3(
4
5　　--是源数据库的逻辑名
6
7　　NAME = Snapshot_Test,
8
9　　--快照文件地址
10
11　　FileName = ‘D:\SqlDate\Snapshot_Test_shot.ss'
12
13)
14
15AS SNAPSHOT OF Snapshot_Test;

结果：数据库快照是只读的的。可以通过sql server的对象浏览器查看可以通过USE(快照库名)来查询数据库快照。

代码如下

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

结果：

id

name

1

hello 1

2

hello 2

3

hello 3

4

hello 4

下边做测试。先删除后利用快照恢复

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

View Code

结果：

id

name

恢复

1RESTORE DATABASE DB_NAME
2FORM DATABASE_SNAPSHOT = 'DB_SNAPSHOT_NAME'
3
4
5
6---
7RESTORE DATABASE Snapshot_test
8FROM DATABASE_SNAPSHOT = 'Snapshot_test_shot'
9
10
11--查询
12use Snapshot_Test;
13go
14SELECT * FROM test

View Code

结果：

id

name

1

hello 1

2

hello 2

3

hello 3

4

hello 4

小提示：快照是只读的，一旦创建不能修改

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

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

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

删除快照

DROP  DATABASE Snapshot_Test_shot

View Code

结果：没有文件，证明被删除

应用场景：在MSDN中如下

　　　　　　维护历史数据已生成报表

　　　　　　使用了可以实现可用性目标而维护的镜像数据库

　　　　　　使数据免收管理失误所带来的影响

　　　　　　使数据免受用户失误带来的影响