自动创建MSSQL Server 2005 数据库快照

苏泽湛

SQL Server 2005集成了很多新功能，其中一项为数据库快照功能，快照功能是数据库[源数据库]的只读、静态试图。多个快照可以位于一个源数据库中，并且可以作为数据库始终驻留在同一服务器实例上。创建快照时，每个数据库快照在事务上与源数据库一致。在被数据库所有者显式删除之前，快照始终存在。本文将讲述如何演示创建数据库快照和自动创建数据库快照

首先，我们来模仿整个快照的创建过程

Step 1 创建数据库DEMO

Create Database demo on Primary
(Name ='demo_Data',
FileName= 'E:\MSSQL2005\MSSQL.1\MSSQL\Data\demo_Data.Mdf',
Size=100MB,
MaxSize=200MB,
FILEGROWTH=10%)
Log on
(Name = 'demo_Log',
FileName= 'E:\MSSQL2005\MSSQL.1\MSSQL\Data\demo_Log.Ldf',
Size=30MB,
MaxSize=50MB,
FILEGROWTH=10%);

　　
　　

　　

Figure 1 .创建DB

Step 2 创建表和数据

Use demo
go
Create table info (Id int, name varchar(100),cname varchar(100))
go
insert into info values (1,'longrujun','沧海笑一声')
insert into info values (2,'zengyu','无梨头')
insert into info values (3,'zhy','美女杀手')
insert into info values (4,'xiongfei','六煞')
insert into info values (5,'hanlei','酒鬼')
go

　　

Fiugre 2.建立表info及插入数据

Step 3 创建快照

use master

go

Create Database demo_Snapshot1 on

(Name ='demo_Data,

FileName= 'E:\MSSQL2005\MSSQL.1\MSSQL\Data\demo_Data.SS1')

AS SNAPSHOT of demo;

Go

此时Demo_Data.ss1快照产生如下图所示

Figure 3.创建快照

Fiugre 4.demo_Data.SS1

Step 4 快照测试：删除源DB中相关数据

Use demo

Go

Select * from info

Figure 5.演示数据

use demo

go

delete from info where id in (2,4)

go

源DB结果如下

Figure 6.测试数据

下面看看快照 demo_Snapshot1中结果

Use demo_Snapshot1

go

Select * from info

go

如图

Figure 7.显示快照数据

Setp 7.自动创建数据库快照

建立一个作业让其每天上午3点按照年月日自动创建快照，用年月日方便大家从快照中恢复数据库

Figure 8.创建job_step

Figure 9. 创建作业步骤

脚本如下

declare @query varchar(1000)
declare @DatabaseName varchar(128)
declare @snapshotName varchar(128)
declare @snapDataName varchar(128)
declare @snapFileName varchar(128)
declare @snapFilePath varchar(128)
declare @nalja varchar(12)
set @nalja=convert(varchar(4), datepart(yyyy, getdate())) + convert
(varchar(4), datepart(mm, getdate())) + convert(varchar(4), datepart
(dd, getdate()))

print 'It is ' + @nalja
Set @DatabaseName ='demo'
Set @SnapDataName='demo_Data'
Set @SnapshotName ='demo_Snapshot'+'_'+@nalja
Set @SnapFilename ='"E:\MSSQL2005\MSSQL.1\MSSQL\Data\demo_Data'+'_'+@nalja+'.ss"'

Print 'Snapshot name is ' +@SnapshotName
select * from sys.databases where source_database_id =db_id(@databasename) and name = @SnapshotName
if @@rowcount <>0
begin
set @query = 'Drop database '+ @SnapshotName
print @query
exec(@query)
end
set @query ='Create database ' + @SnapshotName + ' on (Name = ' +@snapDataName +', FileName=' +@SnapFilename +') AS SNAPSHOT of ' + @databasename+';'
print @query
exec(@query)

调度作业执行时间，如图

Figure 10.作业调度时间

好了，作业已经成功建立，下面看看库中可用的快照

select * from sys.databases where source_database_id is not null

　　
　　
　　

总结一下

1、创建快照的时间可根据应用需求进行调整

2、这篇文章讲述了创建及自动一个数据库快照，通过每天创建数据库快照我们可以便利的回滚到特定日期或比较、监控数据的状态。可以通过快照的恢复命令可以对数据库进行恢复