Waterfall vs. RAD: How to pick the right method for your project

Waterfall vs. RAD: How to pick the right method for your project

How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Again the gr8 article
http://weblogs.asp.net/scottgu/archive/2007/01/11/tip-trick-how-to-upload-a-sql-file-to-a-hoster-and-execute-it-to-deploy-a-sql-database.aspx

Steps to Deploy a SQL Database to a Remote SQL Server without using an
Admin Tool
Below are the steps to take to deploy a local database to a remote
hoster without requiring or using any admin tool:

Step 1: Generate a .SQL File Containing your Schema and Data

The first step to remotely deploy your database in a hosted
environment is to generate a .SQL file that dumps the schema and
content of your database. Follow the steps in my first Database
Publishing blog post to learn exactly how to generate a .SQL file for
either a SQL Express or SQL Server database.

Step 2: FTP up the .SQL File to your Remote Hoster

Once you've generated your .SQL file, upload it to your remote hoster
using FTP or some other file transfer mechanism. Ideally you should
copy this into a protected location where remote users can't easily
get access to it (one suggestion: give it a random name and upload it
into the /app_data folder which is typically protected by default).

The benefit of uploading this file over FTP is that it won't force you
to limit the size of the .SQL file. It can be 100s of megabytes in
size if necessary.

Step 3: Download the RunSQL.aspx Utility Page

Visit this page and download the RunSQL.aspx file linked on it.

The RunSQL.aspx file is an ASP.NET page that the SQL Server team put
together that supports two arguments: 1) The name of a .SQL file, and
2) The connection string of a database. When run the RunSQL.aspx page
will open the .SQL file and iterate over each of its statements and
execute them against the database (indicated via the connection
string). This will provision the database defined within the .SQL
file to the remote database target.

Step 4: Edit the RunSQL.aspx Utility Page

Open and locally edit the RunSQL.aspx file and and configure the name
of your .SQL file, as well as provide the connection-string your
hoster gave you for the SQL database:

Replace the <<YOUR_SCRIPTFILE>> marker and associated
connection-string markers with the correct values for your hosted
configuration. Note that unless you know the fully qualified path of
the .SQL file, you'll probably want to use ASP.NET's
Server.MapPath(fileName) method to calculate the absolute path of the
relative .SQL file path in your web application. For example:

// Filename of the T-SQL file you want to run
string fileName = Server.MapPath("personal.SQL");

// Connection string to the server you want to execute against
string connectionString = @"Server=server123;User
ID=user123;Password=password123;Initial Catalog=MyDBName123";

// Timeout of batches (in seconds)
int timeout = 600;

Step 5: Upload the RunSQL.aspx Utility Page to Your Remote Hoster

Once you have finished updating the fileName and connectionString
values above, upload the RunSQL.aspx file to your remote hoster (for
example: using FTP).

For security reasons I recommend giving the file a random file-name
when you upload it so that other users can't easily find and run it.

Step 6: Hit the RunSQL.aspx Utility Page with a Browser

Once uploaded, hit the remote RunSQL.aspx page via your browser. This
will cause the page on your remote server to parse the .SQL file, and
execute all of its statements. Since the .SQL file contains all of
the database schema creation and population statements necessary to
recreate your database, once the page finishes running you'll have an
identical database deployed at your hoster:

Step 7: Delete the RunSQL.aspx and .SQL Files

Once you've finished running your .SQL script, delete both the
RunSQL.aspx page and the .SQL file from your remote hoster server.

For security reasons you do not want anyone else to be able to
remotely hit the RunSQL.aspx page (since it might recreate your
database causing you to lose data).

Step 9: Update the Web.Config file of your application to point to the
hoster database

The only final step remaining then is to update your web.config file's
<connectionStrings> section to also point at your remote hoster's
database connectionstring value. Once you do this your app should
work fine remotely.

Hope this helps,

--
Regards,
Munish Kalra

http://kalraonaspnet.blogspot.com
http://munishmca.co.nr