解析SQL Server中数据库快照的工作原理

wu123119

 

解析SQL Server中数据库快照的工作原理

 

http://database.51cto.com/art/201104/252827.htm

 

要：SQL Server是典型的关系数据库管理系统，受到广大用户的喜爱，数据库快照是数据库（称为“源数据库”）的只读静态视图。在创建时，每个数据库快照在事务上都与源数据库一致。本文将为大家解析SQL Server中数据库快照的工作原理。

标签：SQL Server  数据库快照

数据快照对数据库中数据来说也是起到了一种保护的作用，即如果源数据库受到了损坏，就还可以将其恢复到创建数据库快照时的状态，对数据库的恢复发挥了很大的作用。

数据库快照是怎样工作的

可以使用典型的数据库命令CREATE DATABASE语句来生成一个数据库快照，在声明中有一个源数据库快照的附加说明。当快照被建立时，同时生成一个稀疏文件。这个文件(只能使用在NTFS卷中)在初始化的时候并没有磁盘空间分配给它——尽管你可能在WINDOWS资源管理器中看到了文件的大小，它会看上去与原始的源数据库文件的大小相同。对磁盘来说其实这个文件的大小接近于零。

数据库快照在初始化时读的数据文件是来自于源数据库的。当源数据库的数据发生变化时，数据引擎就会将原始数据从源数据库拷贝到快照数据库中。这个技术确保快照数据库只反映快照被执行时数据的状态。当SELECT命令被用来发布反对数据库快照时，不管数据页的读取是否被定位在源数据库数据文件中还是在快照数据库数据文件中都是没有锁被发布的。因为在只读数据库快照中是没有锁被发布，数据库快照对于报表解决方案是一个重要的解决方案。

一个快照的实例

现在，让我们来看看数据库快照在SQL Server 2005中是如何工作的。为此，首先我需要一个源数据库作为快照的来源。下面的脚本将创建一个源数据库：

USE master

GO

IF EXISTS(SELECT name from

sysdatabases where [name] = 'SourceDatabase')

DROP DATABASE SourceDatabase

GO

CREATE DATABASE SourceDatabaseON PRIMARY

(

NAME = SourceDatabase_Data,

FILENAME = 'C:SQLServerSourceDatabase_Data.mdf'

) LOG ON

(

NAME = SourceDatabase_Log,

FILENAME = 'C:SQLServerSourceDatabase_Log.ldf'

)

GO

注意这里产品区域的大小。我定义它的大小为CHAR(150)来强调数据文件的增长级数，这样在我接下来的实例中将更容易解释清楚快照是如何工作的。

现在既然我已经有了一个源数据库，现在我装载一些数据来扩展数据文件的大小位。如此，使用上面的脚本来创建销售历史表。

USE SourceDatabase

GO

IF OBJECT_ID('SalesHistory')>0 DROP

TABLE SalesHistory

GO

CREATE TABLE SalesHistory

(SaleID INT IDENTITY(1,1),

Product CHAR(150), SaleDate DATETIME,

SalePrice MONEY)

DECLARE @i INT

SET @i = 1

WHILE (@i <=10000)

BEGIN INSERT INTO SalesHistory

(Product, SaleDate, SalePrice)

VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'),

DATEPART(ms, GETDATE()) + (@i + 57) )

INSERT INTO SalesHistory

(Product, SaleDate, SalePrice)

VALUES ('BigScreen', DATEADD(mm, @i, '3/11/1927'),

DATEPART(ms, GETDATE()) + (@i + 13) )

INSERT INTO SalesHistory

(Product, SaleDate, SalePrice)

VALUES ('PoolTable', DATEADD(mm, @i, '3/11/1908'),

DATEPART(ms, GETDATE()) + (@i + 29) )

SET @i = @i + 1

END

GO

一旦你运行以下的脚本，就可以来操纵数据库文件储存的地方了。在这个实例上面，我将这些文件放在C:SQL Server文件夹下。在我的计算机上，当数据库被初始化创建时数据文件的大小是1,216KB，数据装载后的大小是7,360KB。既然源数据库有一些数据已经在里面了，所以我们现在就能建立一个快照了。使用以下的脚本来建立数据库快照。

CREATE DATABASE SnapshotDatabase

ON

(

NAME = 'SourceDatabase_Data',

FILENAME = 'C:SQLServerSnapshotDatabase.mdf'

) AS SNAPSHOT OF SourceDatabase

这个创建快照的语法与创建数据库的语法非常相似。两个主要的区别：第一个区别是AS SNAPSHOT OF SourceDatabase语句，在服务器实例上指明哪个数据库将作为快照的源数据库;第二个区别是，在事实上数据库快照是不会生成日志文件的。因为没有数据操作事物只会发生在只读数据库上，所以没有也不需要日志。

这个数据库脚本创建一个名为SnapshotDatabase.mdf(数据文件的扩展名不需要mdf)的稀疏文件。如果你在WINDOWS资源管理器中操作这个文件，并同时查看它的属性，你将看到这个文件的大小与源数据库文件的大小相同;然而，实际上磁盘上的大小却接近于零。这时，数据库快照是没有它自己的数据。

你能运行上面我使用的脚本重新在SourceDatabase数据库里面插入10,000行数据到SalesHistory表中。这时，我的SourceDatabase数据库大小是12,480KB，同时，磁盘上我的SourceDatabase数据库大小现在为448KB。这时在SourceDatabase数据库中所改变的数据页都已经被拷贝到SnapshotDatabase数据库中了，这样就能解释为什么在磁盘上它的大小被增加了。

思考

数据库快照允许你为报表创建只读数据库，并且如果有必要的话你可以恢复你的源数据库到你的数据库快照上。同样的，你也可以根据你的报表目的创建任意多个数据库快照。

特别值得注意的是，这些数据库快照会占用磁盘空间，如果有太多的数据库快照的话，它将很快的将你的磁盘阵列填满，尤其在一个产品环境下，如果数据经常要被更新，就更容易将磁盘阵列填满。

另外，使用数据库快照会降低数据库的性能，因为在数据库中将数据页作为执行写操作拷贝是增加了数据库输入/输出。

虽然有这些小缺点，如果你能为报表提出一个好的数据库快照创建方案，SQL Server 2005的这个新的特性将有更多的人使用。

凡事都是有两面性，有好是一面就会有不好的一面，所以大家要理性的去分析，关于数据库快照大家可以根据具体的情况选用，希望上文中讲到的内容对大家能够有所帮助。

【编辑推荐】

1. 教你如何利用SQL Server保护数据
2. SQL Server 数据库故障修复顶级技巧之一
3. SQL Server数据库对于应用程序的关系

【责任编辑：迎迎 TEL：（010）68476606】

上一篇： SQL Server无日志恢复数据库（二） 下一篇： 教你轻松学会SQL Server记录轮班的技巧

 

--------------------------------------------------------------------------------

Sql Server 2005数据库快照

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\MSSQL.1\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\MSSQL.1\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=90
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
在创建完数据库之后，我们马上创建一个快照，创建快照使用的也是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 2005的对象浏览器查看数据库快照，我们可以通过USE [快照库名]来查询数据库快照

Code
use Snapshot_Test_shot;
go
SELECT * FROM dbo.test;
以上语句执行后dbo.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

我们再次查询快照数据库中的数据

快照查询
GO
use Snapshot_Test_shot;
go
SELECT * FROM dbo.test;

二．使用快照恢复数据库

使用快照恢复数据库
Go
--从数据库快照恢复数据库
use master;
GO
RESTORE DATABASE Snapshot_Test from
DATABASE_SNAPSHOT = 'Snapshot_Test_shot';

三． 删除数据库快照

删除快照
--和删除数据库的语法一样
DROP DATABASE Snapshot_Test_shot
在MSDN中数据库快照的典型应用是

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

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

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

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

文章来自学IT网：http://www.xueit.com/Mssql/show-4701-2.aspx

 

 

 

 

--------------------------------------------------------------------------

 

Sql Server 2005 数据库快照创建删除与恢复操作方法
文章来自学IT网：http://www.xueit.com/Mssql/show-1154-2.aspx

数据库快照（atabase snapshot）是一个只读的，静态的数据库视图。一个数据库可以有多个数据库快照,每个数据库快照在被显性的删除之前将一直存在。数据库快照将保持和源数据库快照被创建时刻一致，所以可被用来做一些报表。并且由于数据库快照的存在，我们可以很容易的把数据库回复到快照创建时刻。

数据库快照提供了一个把数据库回复到一个特定时间点的有效途径。一个数据库快照将记录从这个数据库快照被创建后已经提交的所有事务，这样你在对数据库进行错误操作后也不会发出“如果上天能够再给我一次机会的话，我。。。。。。”的感慨。由于是只记录数据库发生的改变，也不是在当前的那一时刻数据库的状态，所以数据库文件并不会很大，如下例：

--我们先来为数据库Northwind创建一个数据库快照，命名为NORTHWIND_DBSS1200，并让此数据库快照的文件存储在C:\NORTHWIND_DATA_1200.SS文件中

CREATE DATABASE NORTHWIND_DBSS1200 ON

( NAME = NORTHWIND, FILENAME =

'C:\NORTHWIND_DATA_1200.SS' )

AS SNAPSHOT OF NORTHWIND;

GO

 

--可以看到这个数据库快照文件的属性，如下：可以看到现在Size on disk为128K

USE NORTHWIND

GO

--现在Northwind数据库进行更新操作

UPDATE DBO.CUSTOMERS

SET COMPANYNAME='NEWEGG.COM'

--可以看到现在Size on disk为384K

--看一下Northwind数据库中被更新的列中存储的内容是已经被更新过的

SELECT DISTINCT  COMPANYNAME FROM NORTHWIND.DBO.CUSTOMERS

 

--看一下NORTHWIND_DBSS1200数据库中被更新的列中存储的内容还是被更新以前的内容

SELECT DISTINCT  COMPANYNAME  FROM NORTHWIND_DBSS1200.DBO.CUSTOMERS

--IF AN ERROR DAMAGES A DATABASE, YOU MAY CHOOSE TO REVERT THE DATABASE TO A DATABASE SNAPSHOT THAT PREDATES THE ERROR. REVERTING OVERWRITES THE ORIGINAL SOURCE DATABASE WITH THE REVERTED DATABASE.

 --从数据库快照中恢复数据库到快照创建的时刻

RESTORE DATABASE NORTHWIND FROM

DATABASE_SNAPSHOT = 'NORTHWIND_DBSS1200'

GO

--确认

SELECT DISTINCT  COMPANYNAME FROM NORTHWIND.DBO.CUSTOMERS

 

--删除数据库快照

DROP DATABASE NORTHWIND_DBSS1200

 

--【创建快照】
CREATE DATABASE Snapshot_Test_20090303 --快照名称
ON
( NAME = 'Snapshot_Test', --不详,可能是mdf的文件名
FILENAME = 'D:\database\Snapshot\Snapshot_Test_20090303.ss' ) --稀疏文件存储路径
AS SNAPSHOT OF Snapshot_Test;
GO

--【恢复快照】
--删除其他任何数据库快照。(有多个快照的情况，只保留一个)
IF EXISTS (SELECT dbid FROM sys.databases
    WHERE NAME='Snapshot_Test_20090302') --假设存在‘Snapshot_Test_20090302’快照
    DROP DATABASE Snapshot_Test_20090302;
GO
--恢复操作要求对源数据库具有 RESTORE DATABASE 权限。
--若要恢复数据库，请使用下列 Transact-SQL 语句： 

--RESTORE DATABASE <数据库名称> FROM DATABASE_SNAPSHOT = <数据库快照名称>
--其中，<数据库名称> 是源数据库的名称，<数据库快照名称> 是要将数据库恢复到的快照的名称。
--注意，必须在此语句中指定快照名称而非备份设备。

RESTORE DATABASE Snapshot_Test FROM DATABASE_SNAPSHOT = 'Snapshot_Test_20090303';
GO


--【删除快照】
DROP DATABASE Snapshot_Test_20090303; -- 删除快照

Ms Sql Server 2005的数据库快照功能使用实践

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

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

客户端可以查询数据库快照，这对于基于创建快照时的数据编写报表是很有用的。而且，如果以后源数据库损坏了，便可以将源数据库恢复到它在创建快照时的状态。 

下面我们实践一下数据库快照的创建和使用：

一．              我们创建一个数据库在此数据库的基础上创建快照

创建源数据库和数据表
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->Use MASTER;
GO
CREATE DATABASE [Snapshot_Test] ON  PRIMARY 
( NAME = N'Snapshot_Test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\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\MSSQL.1\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=90
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

在创建完数据库之后，我们马上创建一个快照，创建快照使用的也是CREATE DATABASE语句，如下：

创建数据库快照
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->--创建数据库快照
create database Snapshot_Test_shot ON
(
    --是源数据库的逻辑名
    Name = Snapshot_Test,
    --快照文件地址
    FileName = 'D:\SqlData\Snapshot_Test_shot.ss'
) 
AS SNAPSHOT OF Snapshot_Test;

 

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

Code
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->use Snapshot_Test_shot;
go
SELECT * FROM dbo.test;
以上语句执行后dbo.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

我们再次查询快照数据库中的数据

快照查询
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->GO
use Snapshot_Test_shot;
go
SELECT * FROM dbo.test;

二．              使用快照恢复数据库

使用快照恢复数据库
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->Go
--从数据库快照恢复数据库
use master;
GO
RESTORE DATABASE Snapshot_Test from 
DATABASE_SNAPSHOT = 'Snapshot_Test_shot';

三．              删除数据库快照

删除快照
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->--和删除数据库的语法一样
DROP DATABASE Snapshot_Test_shot

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

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

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

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

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

 

迄今为止，在我接触的项目中还没有快照的使用场景，本文只是一次简单的对新技术的一次动手实践，大家在实际项目中用过快照吗，请谈谈您的看法。

完整脚本文件 下载

• 首页
• SQL 文章
• SQL 脚本
• SQL 工具
• SQL 书籍
• SQL 试题
• SQL 相关
• 关于我

SQL Server 2005：一个自动生成数据库快照（database snapshot）

 

数据库快照（database snapshot）是 SQL Server 2005 新增的功能，其主要有两大作用：一、使用数据库快照来为报表分析提供静态数据；二、如果误删除或者误更新了数据，可以从数据库快照中找回历史数据。

开发人员或者是DBA在日常操作数据的时候，出现误操作的可能性极大，在公司不到一年的时间内就我见到过大约 5 起误操作事件，大都是在 delete, update 的时候忘记加 where 条件，或者是where 条件不符合逻辑要求。虽然可以通过全备+事务日志备份找回误操作前的数据，但是数据库较大的话，需要花费很长时间来恢复数据，比较麻烦。现在有了数据库快照，好了，只需要利用作业，每天定时创建一个数据库快照（同时删去旧的快照），这样在 24 小时内中的误操作数据都可以从快照中轻松恢复。关于数据库误操作，可以参看： SQL Server DBA 易犯错误（你也要注意了）

如果有多个数据库，手工书写创建数据库快照代码，比较麻烦，特别是当数据库包含多个数据文件。为此，我写了个自动生成数据库快照（database snapshot）的存储过程： master.dbo.sp_gen_snapshot，建议在 master 数据库中创建此过程。

这里简要说明下 sp_gen_snapshot 的参数。我们知道，如果要建立数据快照，必需一个数据库名字： @datbase_name；也需要一个数据库快照名字： @snapshot_name，数据库快照名字是可选的，默认我们以 @database_name + 当前时间 'yyyymmddhhmmssfff' 作为数据库快照名字，例如：Sales_20080101173327653；另外可以指定数据库快照文件存放的路径（目录）@save_path，默认以当前数据库文件的物理路径，作为数据库快照文件存放的路径，但是我建议不要在数据库文件所在的磁盘分区中建立快照文件，否则，数据库文件容易出现碎片；当然，还可以指定数据库快照文件的后缀名 @postfix，默认是：“.ss”（snapshot）；最后 @exec=0 表示只生成数据库快照的 SQL Script，@exec=1 执行创建数据库快照操作。

本文由 www.sqlstudy.com 原创，版权所有，转载请注明作者和出处！

本文链接：http://www.sqlstudy.com/sql_article.php?id=2008070302

• SQL Server：按时间段来批量更新数据的脚本模板（适合处理百万行以上的数据量）
• SQL 小工具——移动数据库物理文件（SQL Server 2005 & 2008）
• SQL Server：Serach database objects in SQL Server 2005