Deploy a Remote SQL Database using a .SQL file

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 here for step-by-step instructions...
   
3. FTP / Upload the .SQL file to your remote hosting account
   Once you have generated the .SQL file, you will need to upload it to your remote hosting account. You can use FTP or any other file transfer methods.
   
4. Download the RunSQL.aspx Utility page
   Download the page from here.
   
   The RunSQL.aspx page has been put together by the SQL Server team and supports two arguments.
   i. The name of the SQL file.
   ii. The connection string of database
   This file will help provision the database mentioned in the .SQL file.
   
5. Edit RunSQL.aspx Utility Page
   Open the file locally and configure the two parameters, namely your database name and connection string. A typical example of the values is given below:
   

   
       // Filename of the T-SQL file you want to run
   
               string fileUrl  = Server.MapPath("myDatabase.SQL");    
   
           // Connection string to the server you want to execute against
   
              string connectionString = @"Server=server123;User ID=user123;
                  Password=password123;Initial Catalog=MyDatabaseName";  
   
          // Timeout of batches (in seconds)
   
             int timeout = 600;

   
   
6. Upload the RunSQL.aspx page to your Remote Hosting Account
   Upload the RunSQL.aspx page now to your remote hosting account using FTP or Frontpage Extensions
   
7. Run the RunSQL.aspx Utility Page in a Browser
   Open the RunSQL.aspx page in a browser so that the code on that page can be executed and the remote database provisioned. You should get a "T-SQL file executed successfully" message.
   
8. Delete the RunSQL.aspx and .SQL file from the remote server now.
   
9. 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 web.config file to your remote server. Now your website or application should work fine with your newly provisioned database.
   

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