Tips and tricks: how to upload a. SQL file to a remote host and execute it for deployment

TipTrick: Howtouploada. SQLfiletoaHosterandExecuteittoDeployaSQLDatabase [original posting date] Thursday, January11, 200712: 20AM last month (English). I discussed the new (free) database Publishing Wizard, it is designed to fully simplify the upload and deployment of SQ in the web host environment

Tip/Trick: How to upload. SQL file to a Hoster and Execute it to Deploy a SQL Database [original posting date] Thursday, January 11,200 AM last month (English), I discussed the new (free) database Publishing Wizard, designed to fully simplify the upload and deployment of SQ in the web host environment

Tip/Trick: How to upload a. SQL file to a Hoster and Execute it to Deploy a SQL Database
[Original article published on] Thursday, January 11,200 AM

Last month, I discussed the new (free) Database Release wizard, designed to fully simplify the upload and deployment of SQL Express and SQL Server databases in the web host environment.

In the first post about database publishing (English), I demonstrated how you can use the database Publishing Wizard to automatically generate. SQL installation file, which contains the definition of recreating your database data (tables, views, stored procedures, triggers, full-text index directories, etc)AndFill in the scripts required by the new database with the same data in your original database. This provides an extremely easy way to construct a. SQL script that completely copies your database on another server:

In my previous post, I mentioned that host providers often provide online SQL management tools. You can use them to open/run your new SQL management tools. SQL file, or SQL query tools are provided to allow you to copy/paste the file. the content of the SQL file is sent to a query execution window to run these SQL statements. Unfortunately, not all host providers support similar tools. Even with host vendors that support similar tools, you may encounter some situations, such as the ones you generate. the SQL file is so large that it cannot be copied/pasted into a text box (submitting a 200 M-byte form usually causes timeout ).

This post demonstrates another method to deploy the. SQL file to the remote host environment. This method does not require your host vendor to configure and install anything for you on the backend.

To deploy a SQL database to a remote SQL server without using the management tool

The following describes how to deploy a local database to a remote host without using any management tools:

Step 1: generate a. SQL file containing data definition and Data

The first step to remotely deploy a database to the host environment is to generate a. SQL file containing the data definition and data of your database. Follow the steps in my first blog post on Database (English) to learn how to generate a. SQL file for SQL Express or SQL Server database.

Step 2: FTP The. SQL file to your remote host

After the. SQL file is generated, use FTP or other file transfer mechanisms to upload it to your remote host. It is best to copy this file to a protected location that is not accessible to remote users. It is recommended that you give the file a random name and then upload it to the/app_data folder, because the content in the file is protected by default.

The advantage of using FTP to upload this file is that it will not force you to limit the size of the. SQL file. If necessary, the file size can be several hundred MB.

Step 3: Download The RunSQL. aspx auxiliary page

Access this page and download the RunSQL. aspx file connected to it.

The RunSQL. aspx file is an ASP. NET page written by the SQL Server product group and supports two parameters: 1). SQL file name and 2) database connection string. Run the RunSQL. aspx page. It will open the specified. SQL file and iterate each SQL statement in the execution file in the database specified by the connection string. This will assign the database defined in the. SQL file to the remote target database.

Step 4: edit the RunSQL. aspx auxiliary page

Open/edit the RunSQL. aspx file locally, set the name of your. SQL file, and provide the connection string corresponding to the SQL database that your host provider gives you:

Set < > Replace the tag and related connection string tags with the correct configuration values in your host environment. Note that unless you know your. the full path of the SQL file. You probably need to use ASP.. NET Server. mapPath (fileName) method to calculate. the absolute path corresponding to the SQL file. 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 auxiliary page to your remote host

After changing the file name and connection string value, upload the RunSQL. aspx file to your remote host (for example, using FTP ).

For security reasons, I suggest you give the file a random file name when uploading the file, so that it is not easy for others to find and execute the file.

Step 6: Use a browser to access the RunSQL. aspx auxiliary page

After the upload, access the remote RunSQL. aspx Webpage through a browser. This will cause the webpage analysis. SQL file on your remote server to execute all the SQL statements. Because. the SQL file contains all the database data definitions and data filling statements required to recreate the database. After the web page is executed, you will deploy an identical database to your remote host:

Step 7: delete RunSQL. aspx and. SQL files

After running your. SQL script, delete the RunSQL. aspx webpage and. SQL file from your remote host server at the same time.

For security reasons, youNoIf you want anyone else to remotely access the RunSQL. aspx webpage, it may rebuild your database and cause data loss.

Step 8: update the Web. Config file of your application to point to the database in the host environment

The last step is to update your web. config file. The connection string value that points to your remote host database. Then, your application should work properly on the remote host.

I hope this article will help you,

Scott

Note: If you want to read other tips, tips, and Recipes posts, visit this webpage.

ASP. NET,. NET, Data, SQL Server, Tips and Tricks