seanprice

SQL Management Studio Snapshot Add-In

I'll assume you know what a database snapshot is, but if not, here is an article from MSDN and here is one from SQL Junkies.

I've found database snapshots to be very handy, but the annoying thing about them is the total lack of support from within the Management Studio UI to create them. Since snapshots are only supported on the Enterprise Edition of SQL 2005 I suspect MS did not see the need to offer a menu option that only certain versions could use.


A database snapshot is simple enough to create via T-SQL but I personally find it more than a little tedious looking up the logical name and the full path to all the data files each time I wish to create one. The problem gets worse when your database has many data files spread across (potentially) different drives.

Since it seemed unlikely that MS was going to add this feature into the next release (I haven't heard anything, at least....), I created an SSMS add-in that adds the [Create snapshot] option to the appropriate context menu.
I've had this kicking around my personal toolkit for some time and have decided to release it to the community as I'm thinking that someone else might find a use for it.

I've created an install package for the add-in which you can download from here.

Once installed, you can right-click on a database node and you'll see the following option in the context menu.

Selecting the [Create Snapshop of...] will then bring up the following window. You can change the default name of the snapshot by editing the textbox or clicking within the listview itself to edit the physical path of the file.


To revert a snapshot, just right click on the snapshot name and you'll see the following menu option.


 The usual caveats apply here.....
I've used this for some time on my machine, and I have released it to other people with no reported problems. However, this does not necessarily mean that you won't have any issues, as I seriously doubt this is bug free. Stick out tongue
Comments, feedback and bug reports are appreciated.

Comments

SimonS SQL Server Stuff said:

This is one of the popular requests, unfortunately its not going to be in Katmai. A number of people

# July 17, 2007 12:39 AM

SimonS' SQL Server Stuff said:

This is one of the popular requests, unfortunately its not going to be in Katmai. A number of people...

# July 17, 2007 12:39 AM

ACALVETT said:

Great to see the snapshot add-in out in the public domain Sean.

Andrew

# July 17, 2007 1:03 PM

Rip Ryness said:

SQL Management Studio Snapshot Add-In

# July 17, 2007 6:19 PM

CoqBlog said:

Sean Price a rendu disponible un petit addin permettant de créer directement dans SQL Server Management

# July 22, 2007 1:02 PM

danielm said:

Interesting add-in but I am more interested in creating one myself - can you point me in the right direction?

# August 9, 2007 3:03 AM

seanprice said:

# August 10, 2007 12:57 AM

danielm said:

Champion Sean - That looks like a great start!

# August 10, 2007 1:27 AM

Jon Sayce said:

Have you ever needed to write some SQL that's almost the same as a previous query, but the previous query

# September 2, 2007 6:59 PM

Charles Hwang said:

This addin does not work on SMSS running on Vista.

I got the following error:

"The add-in 'Snapshot Add-In ' failed to load or caused an exception. Would you like to remove this Add-in?

If you choose yes, you will need to reinstall the Add-in to use it again.

Error messages: Class not registered

Error number: 80040154

Any ideas?

# November 8, 2007 6:07 AM

Glorf IT - Bedenkliches aus dem IT-Alltag » SQL-Server: Datenbank-Snapshots leicht gemacht said:

Pingback from  Glorf IT - Bedenkliches aus dem IT-Alltag » SQL-Server: Datenbank-Snapshots leicht gemacht

# June 8, 2008 9:12 PM