You are here:Home » tsql » Automated Scheduled Snapshot Database

Automated Scheduled Snapshot Database

declare @MyDay varchar(20)
declare @query varchar(1000)
declare @DatabaseName varchar(128)
declare @snapshotName varchar(128)
declare @snapDataName varchar(128)
declare @snapFileName varchar(128)
declare @snapFilePath varchar(128)
set @Myday = (Select datename(weekday,getdate()))
print 'It is ' + @MyDay
Set @DatabaseName ='MyDB'
Set @SnapDataName='MyDB_Data'
Set @SnapshotName ='MyDB_Snapshot'+'_'+@MyDay
Set @SnapFilename ='c:\data\MyDB_Data'+'_'+@MyDay+'.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)

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

create a database snaphot query :


CREATE DATABASE snapdb_0415 ON
( NAME = snapdb, FILENAME =
'C:\backup_2080904\snap_0415.ss' )
AS SNAPSHOT OF snapdb;
GO


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


Restore database from snapshot :

RESTORE DATABASE repldata from
DATABASE_SNAPSHOT = 'repldata_0415';
GO