Share via

Tip#96: Did you know…You could publish your SQL databases using the SQL Publishing Wizard?

  • Article

You can use the SQL Publishing wizard in VS 2008/VWD 2008 to deploy a local database from your development machine to a hosting environment on a remote machine.

This is how you will accomplish this:

Step 1: Create a new web site 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.

You can find my original post  here.

Reshmi Mangalore
SDET, Visual Web Developer.

Comments

  • Anonymous
    September 27, 2009
    of course, i did it two years ago... ^^

  • Anonymous
    October 05, 2009
    @Teklight ...which is how old is original post was. Nevertheless, good tip.

  • Anonymous
    May 27, 2010
    hey, i am running Visual web developer 2008 express and i have ms sql server 2008 express. i have build an website and data connections are working fine. but when i right click on my database it is not showing any option like 'Publish to provider'  i have seen this option on another computer. Please tell me how enable publish to provider. Thanks

  • Anonymous
    June 17, 2010
    @Arbaz: Are you right-clicking on the DB in Server Explorer (not Solution Explorer)?