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.
Comments, feedback and bug reports are appreciated.

Killing ProcessIDs using SMO

"Exclusive Access could not be obtained because the database is in use"

use master
go
alter database <dbname> set single_user with rollback immediate
alter database <dbname> set multi_user

I know some who prefer to explicitly drop the connections through the Activity Monitor and others who use a cursor based solution to cycle through the connections. To each his own...

I ran into this problem recently while using SMO to do a Database Restore. At my company, we have a process where we get an XML
file containing records dropped into a specific FTP folder from a 3rd party. I wrote a listener program that takes that XML file and
imports it into a staging DB. As part of the process, it does a backup of the DB and in the event of an import error, the DB gets
rolled back. This worked fine until I noted some errors in my log with the above "Exclusive Access" message, which led me on a
hunt to find out how to kill the processes using SMO.

After a bit of thinking (OK, and guessing...), I found 2 methods on the Server class itself. One takes a database name as an argument and kills all the running processes for that database.
The other one kills a single process by taking a processID as an argument. 

public void KillAllProcesses(string databaseName);
public void KillProcess(int processId); 

My modified code is below, with the "KillAllProcesses" line added. Hopefully this will be of help to someone else with the same problem. 

internal void RestoreDatabase(string backupFileName, string databaseName)
 {   
    Restore restore = new Restore();
    restore.Database = databaseName;

    restore.Devices.AddDevice(backupFileName, DeviceType.File);
    restore.ReplaceDatabase = true;
    _server.KillAllProcesses(databaseName);
    restore.Wait();
    restore.SqlRestore(_server);
  }