I do a lot of development work and I'm always generating Insert statements for test and development use. I also find I have a recurring need to move data from one database to another and I always seem to be firing up a database synch tool.
I use a variety of diff and snych tools (Apex SQL and RedGate make nice stuff), but at times I wish I had a quick little menu option to script out table data without having to go through the whole process of starting up an external program and walking through a series of wizard steps.
I recalled seeing a SSMS add-in on Code Project that let you right-click on a table and it would generate the insert statements needed, but when I went looking for this, I was unable to find anything except invalid links and page not found errors. Since I already had template code from writing other add-ins, my solution was to take a couple hours and roll my own. (And now that I have done so I'm sure someone will send me a link to another add-in that does the same thing....)
If you have similar data needs then you can download it from here.
The operation of the utility is fairly straightforward.
- After installing, when you right-click on a table node in Management Studio, you will see a "Script Data for [table name] option in the menu.
- Selecting this will bring up a dialog letting you choose a few file options (File, Clipboard, New Query Window) and a few script options. (Disable Constraints, Triggers, etc...)
Since this is just a basic utility, I did not add too many options. It's easy enough to add more options but I did not need them for my requirements so for now, you're stuck with the following...
- All data in the table gets scripted, with the exception of blob fields, which will be replaced with NULL. (This is by design as converting this data to text generates horrendously large files, causing Management Studio to choke on loading.)
- Identity Insert will be enabled if the table has an identity field.
- You cannot chose individual columns to script.
- You cannot chose sub-sets of data (where clause).
If you find yourself needing more options that I have supplied (which is likely), then I suggest checking out one of the stand-alone programs from ApexSQL or RedGate, as they are full of options.
This add-in works best for tables with small amounts of data. If you have a large amount of data then I recommend you use the "Script to File" option, as you are likely to run out of memory. This will of course, vary from machine to machine.
And really, if you have tons of data, you should probably be using BulkCopy as using multiple insert statements is not terrible efficient.
Feel free to contact me with any bugs or suggestions.
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.
"Exclusive Access could not be obtained because the database is in use"
The above line should look familiar to anyone who has done a Database Restore and is easily solved if you are using Management
Studio or Query Analyzer. My personal favorite is to just kick the DB into single user mode, which drops any errant connections.
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
public void KillProcess(int
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 = new Restore();
restore.Database = databaseName;
restore.ReplaceDatabase = true;