Posted by Erland Sommarskog on 04/18/07 22:06

MPD (mpd.jhb@gmail.com) writes:
> How can I create a job in sql agent to create a new snapshot every hour?
>
> I have, for eg a T-SQL that does it manually.
>
> create database Snapshotter_snap_20070418_1821 on
> ( name = Snapshotter, filename =
> 'c:\temp\Snapshotter_snap_20070418_1821.ss')
> as snapshot of Snapshotter
>
> Now, what I do NOT want, is to only have one copy, but rather to do this
> every hour or two through out the day - and keep the old copies for some
> time. (In that case, a DROP database, and a CREATE database <generic name>
> is easy).

Looks likely you could do this with some dynamic SQL:

DECLARE @datestr char(13),
@sql nvarchar(MAX)
SELECT @datestr = convert(char(8), getdate(), 112) + '_' +
convert(char(5), getdate(), 108)
SELECT @datestr = replace (@datestr, ':', '')
SELECT @sql =
'create database Snapshotter_snap_ ' + @datestr + ' on
' ( name = Snapshotter, filename =
''c:\temp\Snapshotter_snap_' + @datestr + '.ss'')
as snapshot of Snapshotter'
EXEC(@sql)




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]