快照的作用
- 快照可用于报告目的
- 维护历史数据以生成报表
- 使用为了实现可用性目标而维护的镜像数据库来减轻报表负载
- 使数据免受管理失误、用户失误所带来的影响
- 管理测试数据库
示例
CREATE DATABASE database_snapshot_name
ON (NAME = 'database_name', FILENAME = 'F:\database_snapshot_name.SNP')
AS SNAPSHOT OF database_name;
再业务调用时需要配置 use database_snapshot_name 或者跨库 database_snapshot_name.dbo.table_name 访问表数据
创建权限
可创建数据库的任何用户都可以创建数据库快照;但是,若要创建镜像数据库的快照,你必须是 sysadmin 固定服务器角色的成员。
快照作业
use master;
go
declare @dbname sysname;
set @dbname = 'SnapShotDatabaseName';
declare @today_date varchar(20);
declare @snapshot_date varchar(20);
select @today_date = convert(varchar(10), getdate(), 23);
select @snapshot_date = convert(varchar(10), crdate, 23) from sysdatabases where name = @dbname;
if (@today_date != @snapshot_date or @snapshot_date is null)
begin
if (@snapshot_date is not null)
begin
declare @s NVARCHAR(1000);
declare tb cursor local
for
select s = 'kill ' + cast(spid as varchar) from sysprocesses where dbid = DB_ID(@dbname);
open tb;
fetch next from tb into @s;
while @@fetch_status = 0
begin
exec(@s);
fetch next from tb into @s;
end;
close tb;
deallocate tb;
exec('drop database [' + @dbname + ']');
end
create database SnapShotDatabaseName on (name='ods_hist', filename='PATH') as snapshot of DatabaseName;
end