I don’t think I’ve written about this tool yet, but it’s one of my favorites.

Microsoft has release (on Codeplex) a SQL Server Hosting Toolkit (it’s free).  The entire toolkit is, I’m sure, full of goodness, but there is one part in particular that I really get a lot of use out of it.  I just shared it with Gerry to help him get his blog back up and running (finally), so I thought I’d post about it here.

The part that I’m in love with is the Database Publishing Wizard (download).  As the name implies, it’s a wizard and it will script out all or part of a database to a SQL file.  If your SQL Server host has installed the corresponding server-side bits, you can publish the database right to your remote DB instead of to file, but I personally like exporting to file.

Anyway, on the first screen you specify a connection to a SQL Server database server (2000 or 2005).  Then you step through a few screens and specify other options, such as which catalog to connect to, which tables to script, the target database version (2000 or 2008), and decide if you want to script the schema, data or both.  Then you “Hit Go” and a moment later you have a single .SQL file, suitable for executing in Query Analyzer, or whatever it is that they call the 2005 version of that.

I’ve used it for backing up a database at a particular point in time, so that, for example, I can revert to a version of the database that matches a particular version of my source (from VSS).  It’s also very handy for moving a database from one server to another, such as what Gerry needed to do.

I can think of a number of things that could be neat features to add, but it could easily get bloated unnecessarily.  So, with that in mind, there are only a couple things that I would like to see changed/added in this app:

  1. Ability to save my settings to a “project file” of sorts.  Perhaps, .DBPPROJ?  I’d rather not have to type in my server name, connection info, and script preferences each time.
  2. I’d prefer the default name for the generated script to be something along the lines of SERVERNAME_CATALOG_yyyymmdd.sql

Those aren’t that big of a deal, really, but I think it would be a relatively small investment on the part of the team that made this thing, and those features could save me minutes of time over the course of a week.  🙂

So if you’re a developer working with SQL Server databases, I’d definitely recommend checking out this piece of free software.