Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
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 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: 1489 | 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...


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones