SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)


By Joe Fernandez,

I created this SP for a school project. Thought others might benefit from it, so I'm contributing it to the community

The SP takes a snapshot of the specified (db_name) database.

By default, it places all snapshots in the c:\temp\backup directory. That directory must exist prior to invoking this SP. You can override this default by specifying the snap_dir input paramter.

The procedure is designed to be invoked any number of times; however, it only keeps the last three snapshots of the specified db_name. You can adjust this by modifying the snap_count local var.

The name of the 3 snapshots = <db_name>snap<1-3>

If all three snapshots exist, the oldest will be dropped and is retaken.

Here's an example flow. The 'first' time this SP is called with a specified db_name of 'FOO', a new snapshot called 'FOOsnap1' is taken. The 'fourth' time this SP is called with a specified DB name of 'FOO', the snapshot with the name 'FOOsnap1' will be dropped and re-taken. The fifth time, 'FOOsnap2' will be dropped and retaken, etc.

A DB admin shouldn't delete any of the three snapshots. But if s/he does, the SP won't be thrown off; however, the 3 will be out of sync and the DB admin will have to visually inspect all three to find the latest. A
simply query like the following will provide the needed info.

select name, create_date from sys.databases d where d.name like 'FOOsnap%'

Required Input Parameters

db_name: the name of the database to take a snapshot of

Optional Parameters

snap_dir: The directory or folder that is to contain the snapshots.
The default is c:\temp\backup.
**** NOTE: this directory must exist! *****

Total article views: 1499 | Views in the last 30 days: 1
Related Articles

Powershell default start directory

This post discusses some methods in configuring the Powershell  default start directory  By default...


SQL Server Default Backup Directory

Someone asked me in a webinar how to change the default backup directory. I knew, but realized that ...


Snapshot each 15 min...

SQL Server 2005 Snapshot


Default Directories

When you create a database without specifying a location for the DATA and LOG SQL Server places them...