Snapshot of databases = Hourly job

[MPD]

Hi

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).

Any help appreciated,
M

[Erland Sommarskog]

MPD (mp*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[MPD]

Thanks Erland - worked a dream!

I am working through my 70-431 course, and this was mentioned a number of
times. However, I see no point in doing this.

Consider:
I take snapshots hourly, on the hour.
At 1.10pm, someone admits a major blunder, and tells me they dropped a table
at 11am.
I can now restore the 11am snapshot to a new DB and recover the table.
But, to do this, I need to delete the other snapshots.
This means that any other "blunders" cannot be recovered from snapshots.

Surely this is not an effective solution?
In fact, the only real use for snapshots I can see, is
To make a snapshot of a mirrored / log shipped database so it can be used as
a static report DB, OR
To make a quick "backup" where a DBA needs to do some work quick and might
risk dataloss through an error.

M

"MPD" <mp*****@gmail.comwrote in message
news:11***************@vasbyt.isdsl.net...

Hi

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).

Any help appreciated,
M

[Erland Sommarskog]

MPD (mp*****@gmail.com) writes:

I am working through my 70-431 course, and this was mentioned a number of
times. However, I see no point in doing this.

Consider:
I take snapshots hourly, on the hour. At 1.10pm, someone admits a major
blunder, and tells me they dropped a table at 11am.
I can now restore the 11am snapshot to a new DB and recover the table.
But, to do this, I need to delete the other snapshots.

I haven't used snapshots much at all, but I did a quick read in Books
Online, and I don't think this is right.

What is correct is that if you decided to revert a snapshot, then all
other snapshots must be deleted. But in that case, at least newer
snapshots would be completely pointless.

But in the case of the big blunder, all you need to do is recreate the
table, possibly scripting it from the snapshot before the blunder, and the
insert the data over. Only the data after that shapshot was taken would
be lost.

The advantage with using snapshots for this sort of recovery is that
you can repair the blunder very quickly, as all data are online. There are
two important drawbacks:
1) Not up-to-the-point recovery.
2) There is an overhead for maintaining the shapshots. (Intially, the
snapshot is an almost empty sparse file. As pages are modified in
the source, pages are copied to the snapshot file.)

The tested and tried method for up-to-the-point recovery is of course
backing up the database and the transaction log regularly. But for a
huge database, making a full restore and apply logs could take quite
some time. And if you don't have the backup on local storage, the cost
for getting it onto the machine is also considerable.

In fact, the only real use for snapshots I can see, is
To make a snapshot of a mirrored / log shipped database so it can be
used as a static report DB, OR
To make a quick "backup" where a DBA needs to do some work quick and might
risk dataloss through an error.

Yes, I think you got it right there. Snapshot is not a good solution
for recovery in general.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Erland Sommarskog (es****@sommarskog.se) writes:

Yes, I think you got it right there. Snapshot is not a good solution
for recovery in general.

I should make that last point more strongly. While snapshots could be
used to make it possible to quickly repair user errors, snapshots can
*never* be a replacement for BACKUP. Keep in mind that a snapshot shares
pages with the source database, so if the source database goes up in
flames because of hardware problems, the snapshot burns with it.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx