SQL中如何用快照,恢复被误删的数据?
共 2723字,需浏览 6分钟
·
2021-04-24 01:58
什么是快照
数据库快照是sql server 2005的一个新功能。MSDN上对它的定义是:
数据库快照是数据库(称为“源数据库”)的只读静态视图。在创建时,每个数据库快照在事务上都与源数据库一致。在创建数据库快照时,源数据库通常会有打开的事务。在快照可以使用之前,打开的事务会回滚以使数据库快照在事务上取得一致。
创建示例数据库
Use MASTER;
GO
CREATE DATABASE [Snapshot_Test] ON PRIMARY
( NAME = N'Snapshot_Test', --数据库名称
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Snapshot_Test.mdf',
--数据文件存放位置及数据文件名称
SIZE = 3072KB ,
--初始容量
MAXSIZE = UNLIMITED,
--最大容量
FILEGROWTH = 1024KB
--增长容量
)
LOG ON
( NAME = N'Snapshot_Test_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Snapshot_Test_log.ldf' ,
SIZE = 504KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%)
COLLATE Chinese_PRC_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'Snapshot_Test', @new_cmptlevel=130
GO
USE [Snapshot_Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
[id] [int] NOT NULL,
[name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
(提示:可以左右滑动代码)
也可以直接在SSMS里通过图形化界面创建测试数据库,这里不作赘述。在创建完数据库之后,我们又新建了一个test表,我们先往里面插入测试数据。如下:
use snapshot_Test;
go
INSERT INTO TEST (id,name)values(1,'hello 1');
INSERT INTO TEST (id,name)values(2,'hello 2');
INSERT INTO TEST (id,name)values(3,'hello 3');
INSERT INTO TEST (id,name)values(4,'hello 4');
GO
SELECT * FROM dbo.test;
结果:
然后我们马上创建一个快照,创建快照使用的也是CREATE DATABASE语句,如下:
创建数据库快照
执行下面的创建快照的语句
create database Snapshot_Test_shot ON
(
--是源数据库的逻辑名
Name = Snapshot_Test,
--快照文件地址
FileName = 'D:\SqlData\Snapshot_Test_shot.ss'
)
AS SNAPSHOT OF Snapshot_Test;
结果:
现在的快照应该和我们新建的数据库一摸一样,可以通过sql server的对象浏览器查看数据库快照,我们可以通过USE [快照库名]来查询数据库快照
use Snapshot_Test_shot;
go
SELECT * FROM dbo.test;
结果:
删除测试库数据
下面我们删除测试数据库中的数据
use Snapshot_Test;
go
DELETE FROM dbo.test;
结果:
使用快照恢复数据库
上面删除是我们模拟误删了数据库中的数据,但是幸好我们做了数据库的快照。可以通过快照来还原刚才被误删的数据。
--语法
RESTORE DATABASE DB_NAME
FROM DATABASE_SNAPSHOT = 'DB_SNAPSHOT_NAME'
我们按照上面的语法进行恢复
RESTORE DATABASE Snapshot_test
FROM DATABASE_SNAPSHOT = 'Snapshot_test_shot'
执行完上面的恢复语句后,我们再查询一下测试库中的表
use Snapshot_Test;
go
SELECT * FROM test
结果:
数据又回来,证明可以通过创建快照来恢复被误删的数据。
注意:
1、快照是只读的,一旦创建不能修改
2、快照只能恢复数据库,不能恢复指定的表
3、为防止误删除,必须在数据插入或更新到数据库之后再创建快照,否则在插入或更新之前创建快照,插入或更新的数据被删除是无法恢复的。
4、快照一般与定时任务一起使用,可以定时创建不同的快照防止数据丢失。
删除数据库快照
和删除数据库的语法一样
DROP DATABASE Snapshot_Test_shot
结果:
数据库快照下面没有任何快照了,说明已经被删除。
应用场景
在MSDN中数据库快照的典型应用是
1) 维护历史数据以生成报表。
2) 使用为了实现可用性目标而维护的镜像数据库来卸载报表。
3) 使数据免受管理失误所带来的影响。
4) 使数据免受用户失误所带来的影响。
![](https://filescdn.proginn.com/dfda3038d5e90e9b941adacad85eb52c/ec8e910644926c7fa03605aecb6a49b3.webp)
推荐阅读
欢迎长按扫码关注「数据管道」