SQL Database Publishing wizard is now in Visual Studio 2008.

SQL Database Publishing wizard is now in Visual Studio 2008.

  • Comments 17

The SQL database publishing wizard is a very popular web-downloadable add-in for Visual Studio 2005. The publishing wizard addresses the issue where a developer needs to deploy a local database from his development machine to a hosting environment on a remote machine. We received a lot of positive feedback for this wizard and we decided to integrate this with Visual Studio 2008. This is a feature that was added post Beta2 and will be available with Visual Studio 2008 RTM.

 

The wizard supports two key database hosting deployment scenarios:

 

1.      It generates a single .SQL script file which can be used to recreate a database on a remote machine

Using the Database Publishing Wizard  you can point to a database on your local machine, and then automatically create a .SQL script file that contains the setup logic needed to re-create a replica of the database on any remote system - for example an external hosting system. This .SQL script includes everything needed to create the database schema (tables, views, sprocs, triggers, full-text catalogs, roles, rules, etc). You also have the option of populating the new database with the same data contents as your local tables. Most hosters today support the upload and running of .SQL files to their hosted environments using their admin control panels. So, all you need to do is upload and run the .SQL script generated by the Database Publishing Wizard, and you will have a working database in your hosted environment. This should considerably reduce the effort required to deploy your databases.

2.      It connects to a web service provided by your hoster and directly creates objects on a specified hosted database.

 

The Database Publishing Wizard also enables you to point at a database you are working with on your local system, and then use web-services to transfer and recreate the database in your remote hoster environment (without you having to create the .SQL file or use the hoster admin control panel to run it).  This publishing option does require that a SQL Publishing web-service be exposed in the hosting environment, and the SQL Server Hosting Toolkit includes a free implementation of this SQL Publishing web-service that we'll be working with hosters to aggressively deploy

 

The release version of  Visual Studio 2008 will come pre-installed with the 1.2 version of SQL Publishing Wizard.

 

So now that you know what the Database Publishing wizard does, let us dig deeper into how it will work with Visual Studio 2008.

 

Step 1: Create a new website by selecting menu File ->New Web Site. Switch to Server Explorer and add a new Data connection and connect to a database. In this case we will use the Northwind database that comes with SQL Express. You should point to the database you want to publish.

 

Step 2: Select Northwind.dbo node in Server explorer and right click to bring up the context menu. In the Context menu you have a “Publish to provider…” option.

 

 

Step 3: Click  “Publish to provider …” to launch the Database Publishing Wizard.

 

 

Step 4: Click Next to select the mode. Let us go with “Script to file” mode. We will need to specify the .SQL file name and location.

 

 

Step 5: Click Next and you will get to the Publishing Options. On this page, select the script for target database (SQL Server 2000 or SQL Server 2005) and the types of data to publish (Schema, Data or Schema+Data). You also have the option to drop existing object in script if you want to.

 

 

Step 6: Keep the default selection and hit next and generate the .SQL script.

 

Step 7: The .SQL file  generated contains a script that you can run on any SQL server to re-create all the tables, sprocs, views, triggers, full-text catalogs, etc. for a database, as well as import and add all of the table row data that was in the database at the time the .SQL file was created. 

 

Step 8: The .SQL file is a plain text file. You can open it in your favorite editor and customize it as you need.

 

Step 9: Now that we have our .SQL files, we can go about using them to install our database at our hoster. Exactly how to install the .SQL files will vary depending on how the hoster give access to our SQL account.  Some hosters provide an HTML based file-upload tool that allows you to provide a .SQL file - which they will then execute against the SQL database you own. 

Other hosters provide an online query tool (like below) that allows you to copy/paste SQL statements to run against your database.  If you have a hoster which provides an online query tool like this, then you can open the .SQL file with a text-editor and copy/paste the contents into the query textbox and run it.

 

 

And that is all there is to using the Database Publishing Wizard. Hope this feature is useful to you.

 

Scott Guthrie also has a nice blog entry on this. You can read it  here.

 

~ Reshmi Mangalore

Leave a Comment
  • Please add 6 and 3 and type the answer here:
  • Post
  • PingBack from http://www.artofbam.com/wordpress/?p=8840

  • Since 2005, SQL Server Express has shipped with both Visual Studio and Visual Web Developer Express.

  • Since 2005, SQL Server Express has shipped with both Visual Studio and Visual Web Developer Express.

  • Where is the 1.2 release on CodePlex, I can't see it? Is it coming soon?

    I hope they added some support for adjusting collations, because we always encounter a nasty bug in the generated scripts and have to search/replace two pieces of code.

  • Hi Mike,

    The 1.2 version of  SQL Publishing Wizard was built exclusively for Visual Studio 2008 and it will not be available on Codeplex.

    I'm afraid we have not made any changes to collations for Visual Studio 2008. Can you send us more details about the error you are seeing - the generated script, the lines causing the problem, the error message, etc? We can investigate the cause of the issue. However, as you know, we are now locking down the product, and will most likely not be able to fix and test it in time for release. We will definitely consider this in the next release of Visual Studio.

    Thanks,

    Reshmi Mangalore

  • I seem unable to use the datapublishing wizard. It is not on my copy of vs2008. Help

  • I just installed vs2008 and I am unable to find the publishing wizard.

  • The SQL Database Publishing Wizard was added post Beta2. So, if you have a copy of Beta2 installed you will not see the wizard. This feature will be  available in Visual Studio 2008 RTM.

    Regards,

    Reshmi Mangalore

  • Hello,

    Will this Database Publishing Wizard be included in Visual Web Developer Express 2008 or this is just limited to VS2008 only.

    It would be nice if you can include in Express Products as well.

    Thanks

  • Hello IronRuby,

    The SQL DB publishing wizard will be available in both VS 2008 and VWD Express 2008.

    --Omar Khan

    Group Program Manager

    Visual Web Developer

  • Here is the latest in my link-listing series . Also check out my ASP.NET Tips, Tricks and Tutorials page

  • Thanks for including the SQL DB Publishing Wizard in both VS 2008 and especially, VWD Express 2008.  I'm looking forward to using this feature.

  • Une nouvelle que j'ai totalement loupé : http://blogs.msdn.com/webdevtools/archive/2007/10/15/sql-database-publishing-wizard-is-now-in-visual-studio-orcas.aspx

  • I'm looking forward to taking advantage of this functionality!  I've used DB Pro for a while now, but I like the option to migrate data as well.

  • Ya hemos hablando de esta excelente herrramienta , que nos permite generar archivos script (con soporte

Page 1 of 2 (17 items) 12