Scripting SQL Server Database Data

  • Article

There have been many times when I've needed to script the creation of SQL Server database data; usually for source control management of core data that, in a given application, is relatively static and needs to be there at install time. It has also been useful for re-creation of test data.

 But how to create such scripts?

There are a number of tools and the one I've usually used is my old favourite CodeSmith, which comes with a template for scripting database data. You tell it the tables in an existing database you want scripted, and it will go in there and plough through them all and make a single SQL script of INSERT statements representing every last byte of data in them. It is not that smart however and often creates scripts that will not immediately work when executed against SQL Server due to un-escaped characters or the mistreating of certain data types. I've usually had to use these scripts as a starting point and hand-edit them until they are what I need.

Today I discovered a new tool that does a vastly better job of this, however - the "Microsoft SQL Server Database Publishing Wizard". It's purpose is actually to facilitate the deployment of SQL Server databases into a hosted environment on either a SQL Server 2000 or 2005, using a set of separately-supplied Web Services that the hosting environment needs to host. One of the options of the tool, however, is to simply script the database you want to deploy, and when I tried this out it did an excellent job, even inserting the occasional PRINT statement to indicate the progress of large numbers of INSERTS. It will script the schema, too if that would be useful. Best of all, the tool is free! So if you find you could have a use for version controlled, scripted INSERT data this tool will create it for you from an existing database and save you a great deal of hassle.

You might also find the real purpose of the tool - publishing a database to a hosted provider - hugely convenient too. When I tried it out on my own private server it worked like a charm and certainly looks like it is a much easier way of getting a database from a local machine to an ISP. However I've not been able to find any public ISP's that provide this functionality (via the supplied web services) to their users - does anybody know of any? I'd be interested in taking a look.