I remember seeing the database publishing wizard for the first time when it was a planned Toolkit and not even integrated with VS2008 yet. I was very excited about being able to publish my databases so easily to my web host. Then with a gazillion other features like LINQ and EF, myriad additions to ASP.NET and more, I forgot about it.

I just rediscovered this great tool (now with more improvements) in Visual Studio 2010 and was a little embarrassed when I remembered that it's been in VS2008 all along. But nobody needs to know about that. ;-)

The wizard lets you create a script of database schema and data. It is similar to the create script wizard in SQL Server Management Studio with one notable difference. In SSMS for SS2008, there is an option to script the data but in SS2005 there is no such option. However, whether you are in VS2008 or VS2010, targetting SS2000, SS2005 or SS2008, you can script both the schema and/or data. I wish I had realized this a long time ago.

The wizard is available from the VS Server Explorer, therefore you need to have made a connection to a database already.

Here's a quick run through.

"Publish to Provider" is available from the context menu of the database connection in Server Explorer.

The database will already be selected.

Like the wizard in SSMS, you can choose to have the wizard just script everything in the database or you can explicitly choose which objects (tables, stored procedures, etc.) to create scripts for.

If I had unchecked the default (Script all objects...) then there will be additional wizard pages to make the explicit object selections.

Here's what makes this wizard extra sweet. You can simply save the script to a file, or publish to a hosting provider which will automatically run the script for you on your host db. Note the warning that existing objects will be overwritten. That means if you run a create table script, a pre-existing table WITH DATA will be replaced entirely with whatever the script creates (which could be only schema or schema and data).

Note that your web host needs to support database publishing with a specific web service.

On the options page, you can choose between SS2000, 2005 and 2008 and also choose to publish Schema and Data, Schema Only or Data Only.

You'll get the same progress window as SSMS gives you when it's all done. I have only created the local SQL file in this case.

On the surface, this wizard looks just the same in Visual Studio 2010 as it does in VS2008. However, do not miss the new One-Click Web Deployment features in VS2010 which will publish your website and database together.

Scott Forsyth from Orcsweb has a great blog post, Visual Studio 2010 1-Click Publishing, on this feature. Vishal Joshi (from the ASP.NET team) has a post about the database specific feature of the web deployment: Database Deployment with Visual Studio 10 .