SQL SERVER 用存储过程 自动创建维护数据库快照

下面方法,是用存储过程的方式来自动创建数据库快照,这样只要再创建一个作业来调度,就可以实现每个小时建立一个快照.

同时还可以用后面的过程自动删除前一天的快照,这样就保证数据库始终保留一天的快照.

调用方式

--生成快照

exec  usp_CreateDatabaseSnapshot 'databaseName','C:\'

--删除一天前的所有快照

exec usp_DropDatabaseSnapshot 

过程

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

Create proc [dbo].[usp_CreateDatabaseSnapshot]( @DatabaseName varchar(20),@Filepath varchar(100))

as
set nocount on
declare @query varchar(1000)
declare @snapshotName varchar(128)
declare @snapDataName varchar(128)
declare @snapFileName varchar(128)


declare @RQ varchar(12) --
set @RQ= convert(varchar(12), getdate(),112)+convert(varchar(4), datepart(hh, getdate()))


Set @SnapDataName=@DatabaseName+'_Snapshot_'+@RQ --快照数据库
Set @SnapshotName =@DatabaseName+'_Snapshot_'+@RQ --数据库文件


--已建立就删除
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
-- 临时表,生成快照文件路径  ,如果有多个数据文件,就省心了,不用一个个的命名了.
CREATE TABLE #dbfiles
( 
dbname varchar(100),
filetype varchar(100),
filename varchar(200)
) 


declare @sql varchar(1024) --临时sql 语句 


set @sql = 'INSERT INTO #dbfiles SELECT ''' + @DatabaseName + ''',name,filename  from ' + @DatabaseName + '.sys.sysfiles where groupid>0';
EXEC(@sql)  


declare @snapFileString varchar(max)
set @snapFileString=''
select @snapFileString=@snapFileString+'(Name='+filetype+',filename="'+@Filepath+'\'+filetype+'_Snapshot_'+@RQ+'.ss"),'+char(10)
from #dbfiles
drop table #dbfiles --删除临时文件


--生成数据库快照
set @query ='Create database ' + @SnapshotName + ' on '+left(@snapFileString,len(@snapFileString)-2)+' AS SNAPSHOT of ' + @databasename+';'


exec(@query)

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

自动删除前一天的快照.

Create proc [dbo].[usp_DropDatabaseSnapshot]
as 
declare @query varchar(5000)
set @query=''
select @query=@query+'Drop database '+name+';'+char(10) from sys.databases where source_database_id is not null 
and create_date<getdate()-1
--print @query
exec(@query)