Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database
Last month I posted about the new (free) Database Publishing Wizard that is designed to make it much, much easier to upload and deploy SQL Express and SQL Server databases in a web hoster environment.
In my first Database Publishing post, I walked through how you can use the Database Publishing Wizard to automatically generate a .SQL installation file that contains the script necessary to recreate your database schema (tables, views, sprocs, triggers, full-text catalogs, etc) and also populate your database with the same table row contents as your original database. This provides a super easy way to put together a .SQL script that entirely automates replicating your database on another server:
In my previous post I mentioned how hosters often provide an online SQL management tool that you can then use to open and run your newly generate .SQL file, or provide a SQL query tool that allows you to copy/paste the .SQL file's contents into a query execution window to run. Unfortunately, though, not all hosters support tools like this. And even with hosters that do support it you might run into cases where your generated .SQL file is so big that copying/pasting it into a text box isn't really viable (doing a form post of 200MB of content will typically time out!).
This blog post walks through an alternative way to deploy your .SQL files in a remote hosting environment, and which does not require your hoster to configure or install anything on the backend.
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:
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,
Scott
P.S. For more of my ASP.NET Tips, Tricks and Recipes, please visit this page.