SQL Server 灾难恢复之数据库快照

SQL Server 灾难恢复之数据库快照

数据库快照，是SQL Server2005中第一次引入的技术，利用数据库快照可以还原用户数据库。但数据库快照不能替代数据库备份，快照和备份是两回事，数据库快照只是用在特殊场合，它的速度和效率会高于备份，但不能替代备份。只能说是一种另种备份的方式。咱们讲到后面备份的时候，咱们会对数据库快照和备份做一个对比。除了还原用户数据库之外，我们还要看一下，如何还原系统数据库。很多朋友可能在SQL 2000的环境下还原过系统数据库，那么在SQL Server 2008里，对系统数据库的还原有什么不一样？SQL Server2008所支持的恢复模型有哪些？以及所支持的备份方式有哪些？

下面咱们就看第一个内容：

数据库快照    

什么是数据库快照：它是源数据库的只读的静态视图。什么意思？可以理解为数据库快照是一个视图，但是只能读，不能修改。也就是说我们不能通过快照来修改源数据库的内容。但可以查询数据库快照中的内容。但查询到的内容是不是真正是快照中的内容，这个就不一定了。这就得来看一下数据库快照的工作原理。

数据库快照工作原理：

数据库快照在数据页级运行，在第一次修改数据库页之前，将原始数据页从源数据库复制到快照，此过程称为“copy-on-write”写入时复制。对于修改页中的记录进行后续更新不会影响快照的内容。对要进行第一次修改的每一页重复此过程。这样，快照将保留自创建快照后经修改的所有数据库的原始页。（静态）

解释：数据库快照是在数据页级运行的，这是一个工作的前提。咱们知道在SQL Server中，所有的数据是存放在数据页中的，当我们对某个数据库创建数据库快照时，这个时候快照中是没有内容的，也就是没有数据的，我们只要修改了数据了，也就是说数据页发生了变化。那么这个时候，发生变化的数据页的内容就补复制到快照中。当我们对数据库进行了修改（writer）那么就复制到快照中（copy）。并且只存放每一个原始页的内容，那么说这个数据页发生了很多次变化，那么快照中也只会保存第一次的原始页的内容，也就是你第一次修改之前的原数据。你以后修改多少次，跟我没有关系。每一个页都是如此。

在SQL Server 2008中使用“稀疏文件”来存储复制的原始页。最初，稀疏文件就是一个空文件，里面是没有任何数据的，然而，由于数据库随着时间的推移不断更新，稀疏文件会增长为一个很大的文件。如下图所示：

系统将原始页移动到快照中，而源数据库中存放的是修改后的数制。

数据库快照读操作原理

用户可以查询数据库快照中的数据，但是分为两种情况：

1 如果数据发生改变了，则访问的是数据库快照中的数据，如下图所示：

说明了对访问源数据库中更新页的快照的读操作。此读操作有1个页面是从快照中读取的，因为该页已经被更新过，而另外9个页面是从源数据库中读取的。

2 如果未更新源数据库中的页，则对快照的读操作将从源数据库读取原始页。下图显示了对新创建的快照（其稀疏文件不包含页）的读操作。此读操作仅从源数据库读取。

更新模式对数据库快照增长的影响

如果您的源数据库过大并且您担心磁盘空间使用量，则您应该在某个时候用新快照替换旧快照。快照理想的使用期限取决于其增长率以及可用于其稀疏文件的磁盘空间。快照所需的磁盘空间取决于在快照使用期限内源数据库中更新的不同页的数量。因此，如果大多数情况下更新重复更新的页的小子集，则随着时间的推移，增长率会降低，快照所需空间也会相对较小。相反，如果最终将所有原始页至少更新一次，则快照将会增长到源数据库的大小。如果磁盘将满，则快照会互相争用磁盘空间。如果磁盘驱动器已满，则无法将操作写入所有快照。

总结：更新的频率决定了快照的大小，快照的大小决定了磁盘空间的使用量。一定要计算好磁盘空间的使用量。

使用数据库快照的场合：

1 生成报表维护历史数据

例如，每一季度都需要生成一个报表，那么我可以在生成报表之前可以做一个快照，这个报表就是根据此快照生成的。因为数据库快照里存放的是修改过的原始数据，我只是把第一个季度发生更新的数据放到一个快照里。所以，在这里只需要周期性的为每个季度生成快照，再根据此快照生成相应的报表。

2 保护数据防止出现误操作

管理员或用户的误操作，如误更新，如删除等。

数据库快照的限制：

1数据库快照必须与源数据库在相同的服务器实例上创建和保留。

2快照是只读的

3不能备份快照

4快照不能被分离和附加

5不能在FAT32分区上创建快照

6数据库快照反映了文件组的状态   联机文件组将保持联机状态，脱机文件组将保持脱机状态，如果在源数据库中联机了，也不能访问。

7要删除数据库，需要先删除此数据库上的所有快照

8 数据库快照将继承快照创建时其源数据库的安全约束。 由于快照是只读的，因此无法更改继承的权限，对源数据库的更改权限将不反映在现有快照中。 如果源数据库中更改了，不允许某用户访问，也不会影响到快照。

9 数据库快照不能创建全文索引

创建数据库快照：

创建数据库快照之前，考虑如何命名它们是非常重要的。每个数据库快照都需要一个唯一的数据库名称。为了便于管理，数据库快照的名称可以包含标识数据库的信息，例如：

1源数据库的名称。

2该新名称用于快照的指示信息。

3快照的创建日期和时间、序列号或一些其他的信息（例如一天中的某个时间）以区分给定的数据库上的连续快照。

语法：

Create database 快照名

On

(name=’数据文件的逻辑名称’，  --源数据库有几个数据文件，就需要几个name和filaname

Filename=’快照稀疏文件的路径及文件名’)  --扩展名随意可以无

As  snapshot of 源数据库名

Demo:

例如：现有DB1数据库，只有一个数据文件，那么创建快照的代码如下：

create database db1_snapshot_20110420

on

( name='db1',

filename='c:\sqlsnapshot\db1_data_20110420'

)

as snapshot of db1

查看快照文件：

如果源数据库有多个数据文件，但只给出一个snapshot文件，则会报错，例如：

如何使用数据库快照：

当不小执行了一个误操作，可以通过快照来还原数据库。

Demo：使用数据库快照还原误删除的记录、误更新的数据、误删除的表

drop database db1_snapshot_20110420

drop database db1

create database db1

use db1

create table yg (id int,uname varchar(8))

insert into yg values (1,'zs')

insert into yg values (2,'ls')

insert into yg values (3,'ww')

use db1

sp_helpfile

create database db1_snapshot_20110420

on

(name='db1',filename='c:\sqlsnapshot\db1snapshot')

as snapshot of db1

下面一边修改或删除数据，一边观察快照文件的大小

--误删除一条记录

delete from yg where id=1

分析，为什么源数据库的yg表是两条记录，而快照的yg表中是三条记录

下面开始还原：

insert into db1..yg select * from db1_snapshot_20110420..yg where id=1

select * from yg

1 zs

2 ls

3 ww

--误更新了一条记录

update  yg set id=222 where ID=2

select * from yg

select * from db1_snapshot_20110420.dbo.yg

update yg set id=(select id from db1_snapshot_20110420..yg where id=2) where uname='ls'

--还原误删除的数据表

步骤：

1在数据库快照中生成对象的脚本

2在源数据库中执行该脚本

3重新还原数据

--误删除整个表

drop table yg

1 在数据库快照中生成该对象的脚本

2 在源数据库中执行该对象

复制脚本到源数据库中，并删除开头和结尾的一些无关语句

CREATE TABLE [dbo].[yg](

[id] [int] NULL,

[uname] [varchar](8) NULL

) ON [PRIMARY]

3 开始还原数据

insert into yg  select * from db1_snapshot_20110420..yg

select * from yg

DEMO：还原数据库中所有的对象

语法： restore database 源数据库名  from database_snapshot=’数据库快照名’

例如：Create database db2

use db2

create table yg1 (id int,uname varchar(8))

insert into yg1 values (1,'a')

create table yg2 (id int,uname varchar(8))

insert into yg2 values  (1,'a')

create database db2_snapshot

on (name='db2',filename='c:\sqlsnapshot\db2')

as snapshot of db2

select * from yg1 

select * from yg2

drop table yg1,yg2

use master

restore database db2 from database_snapshot='db2_snapshot'

select * from db2..yg1

select * from db2..yg2

当一个数据库存在多个数据库快照时，是不能还原其中的任何一个数据库快照的，必须要把除了要恢复的快照保留外其他快照全部删除，然后才能从快照中恢复数据库。

DEMO:我们现在对db1这个数据库再建一个快照，让它有两个快照。

create database db2_sna2

on (name='db2',filename='c:\sqlsnapshot\db22')

as snapshot of db2

restore database db2 from database_snapshot='db2_snapshot'  --报错

drop database db2_sna2

restore database db2 from database_snapshot='db2_snapshot'  --OK

注意：还原之后，数据库的日志会重建，原来的日志就没有了，所以此时应该做一个全备份。

可以根据需要，自动化创建数据库快照：

参考脚本：

declare @str1 nvarchar(500)

declare @str2 nvarchar(500)

declare @str3 nvarchar(500)

set @str1=cast(year(GETDATE()) as varchar(4))+cast(MONTH(GETDATE()) as varchar(2))+cast(DAY(GETDATE()) as varchar(2))

set @str2='create database db1_snap'+ @str1

SET @str3=@str2+@str1+' on (name=[db1],filename=[c:\sqlsnapshot\'+@str1+']) as snapshot of db1'

exec sp_executesql @str3

新建作业：每天凌晨执行一次

通过命令：

Select * from sys.database_files 可以查看快照和源数据库的对应信息