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)
Thank this author by sharing:
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! *****
This post discusses some methods in configuring the Powershell default start directory
Someone asked me in a webinar how to change the default backup directory. I knew, but realized that ...
SQL Server 2005 Snapshot
When you create a database without specifying a location for the DATA and LOG SQL Server places them...