生成数据库快照

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(50) 
set @nalja=convert(varchar(25),getdate(),112)+'_'+convert(varchar(2),getdate(),108)
Set @DatabaseName ='CR_YPLS8_JMD'--数据库名称
Set @SnapDataName='ChaoRan_Data'--数据库逻辑名称
Set @SnapshotName ='CR_YPLS8_JMD'+'_'+@nalja--快照名称
Set @SnapFilename ='"I:\CR_BACKUP\数据库快照\'+@SnapshotName+'.ss"' 
set @query ='Create database '  + @SnapshotName   + ' on  (Name = '  +@snapDataName +', FileName='  +@SnapFilename +') AS SNAPSHOT of '  + @databasename+';'

exec(@query)
--------------------------------------------------------------------
set @nalja=convert(varchar(25),getdate(),112)+'_'+convert(varchar(2),getdate(),108)
Set @DatabaseName ='CR_YPLS8_SG'--数据库名称
Set @SnapDataName='ChaoRan_Data'--数据库逻辑名称
Set @SnapshotName ='CR_YPLS8_SG'+'_'+@nalja--快照名称
Set @SnapFilename ='"I:\CR_BACKUP\数据库快照\'+@SnapshotName+'.ss"' 
set @query ='Create database '  + @SnapshotName   + ' on  (Name = '  +@snapDataName +', FileName='  +@SnapFilename +') AS SNAPSHOT of '  + @databasename+';'
--print @query
exec(@query)
/*
set @nalja=convert(varchar(25),getdate(),112)+'_'+convert(varchar(2),getdate(),108)
Set @DatabaseName ='CR_YPLS8'--数据库名称
Set @SnapDataName='ChaoRan_Data'--数据库逻辑名称
Set @SnapshotName ='CR_YPLS8'+'_'+@nalja--快照名称
Set @SnapFilename ='"I:\CR_BACKUP\数据库快照\'+@SnapshotName+'.ss"' 
set @query ='Create database '  + @SnapshotName   + ' on  (Name = '  +@snapDataName +', FileName='  +@SnapFilename +') AS SNAPSHOT of '  + @databasename+';'
--print @query
exec(@query)
*/

删除3天前的快照

DECLARE @data VARCHAR(50),@sql VARCHAR(500)
set @data =convert(varchar(25),getdate()-3,112)
set @sql='if db_id(''CR_YPLS8_JMD_'+@data+'_00'') is not null  DROP DATABASE CR_YPLS8_JMD_'+@data+'_00' 
EXEC(@sql)
---------------------------------------
set @sql='if db_id(''CR_YPLS8_SG_'+@data+'_00'') is not null  DROP DATABASE CR_YPLS8_SG_'+@data+'_00' 
EXEC(@sql)
---------------------------------------
/*
set @sql='if db_id(''CR_YPLS8_'+@data+'_00'') is not null DROP DATABASE CR_YPLS8_'+@data+'_00' 
EXEC(@sql)
*/