How to deploy / publish a local SQL Database to the remote SQL server

Deploying the local SQL database (both schema and data) to the remote SQL Serever provided by us has always been a challange for most developers and individuals.

To make life easier for you, the SQL Server team has published the release candidate of a new SQL Server Hosting Toolkit which will make publishing your local database to the remote server much, much easier now. It allows you to work with SQL Express, SQL Server 2000, and SQL Server 2005 databases locally, and then easily transfer your complete database (schema & data) to your shared hosting remote SQL Server with us.

The database Publishing Wizard lets you select your local Sql Database and create a .SQL file containing the setup logic needed to recreate the exact replica of the database on the remote system.

1. Download and Install the Database Publishing Wizard.
   Click here to download and install it.
   
2. Creating .SQL Installation Scripts for the database.
   Launch 'Microsoft SQL Server Database Publishing Wizard' and follow the instructions to create your .SQL file.
   
   
   Click Next here
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
3. Using the .SQL file to create your remote database.
   You can use an online query tool that allows you to copy/paste SQL statements or the .SQL file generated to run against your database.
   
4. Update your connection string in web.config file.
   Finally you will need to update the connection string in your web.config file and ftp the file to your remote server.

For more details on this topic, you can visit Scott Guthrie's blog here: http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx