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:

    // 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,

Scott

P.S. For more of my ASP.NET Tips, Tricks and Recipes, please visit this page.

Published Thursday, January 11, 2007 12:20 AM by ScottGu

Comments

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Thursday, January 11, 2007 7:44 AM by Ali Khalid

Hi,

 Thanks for the great post. I just wanted to know if the Database Publishing Wizard is supported by the www.vwdhosting.net, Since me and lots of other students I know, use this site to test our Web Apps.

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Thursday, January 11, 2007 8:08 AM by Mark Wisecarver

Very cool Scott, thanks a bunch bro.

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Thursday, January 11, 2007 12:13 PM by Morten

WOW! Classic ASP style inline-script ! :-)

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Thursday, January 11, 2007 12:19 PM by Frank

Hi Scott,

sorry to ask this question but I am so short on time right now that I cannot run a test myself right away.

My question is: wouldn't SQLCMD work as well when I run it locally and connect then to my remote database?

What speaks against using SQLCMD? Would it time-out or something when executing big .SQL files?

I would appreciate your insight.

Thanks and best regards,

Frank

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Thursday, January 11, 2007 2:15 PM by ScottGu

Hi Ali,

I don't believe the backend service for the Database Publishing Wizard is deployed yet on vwdhosting.net - but you can absolutely use the steps I outlined above to deploy a database using the Database Publishing Wizard there.

Hope this helps,

Scott

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Thursday, January 11, 2007 2:18 PM by ScottGu

Hi Frank,

Yep - you can definitely use SQLCMD or the SQL utility I blogged about here (http://weblogs.asp.net/scottgu/archive/2005/12/15/433284.aspx) with .SQL files.

The challange with a lot of hosted environments, though, is that they don't give you access to these (or don't directly expose the SQL database on the Internet).  That is why the steps above are useful - they will let you run the .SQL script anywhere (even when the database is behind a firewall and all you have access is to a web root via FTP).

Hope this helps,

Scott

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Thursday, January 11, 2007 5:49 PM by Liming Xu

I've recently created a web installer for sql scripts and used the logic similar to it. By looking at the aspx soure code, there are some flaws in it.

Of all the custom scripts (non aspnet) store procedures, the script doesnt' grant access to the user account of the remote db, so when you try to run your app, u'll get an exception that goes something like

EXECUTE permission denied on object '[your store procedure]', database '[your datagbase]', owner 'dbo'.

to do that, you'll need to add one more function

protected bool GrantPermissionToUser()

   {

       bool success = false;

       //replace with your own

       String your_db_username = "liming";

       String sql =    "DECLARE @ExecSQL varchar(100) " + Environment.NewLine +

                       "DECLARE curGrants CURSOR FOR " + Environment.NewLine +

                       "SELECT 'GRANT EXECUTE ON ' + NAME + ' TO "+your_db_username+"'  " + Environment.NewLine +

                       "FROM SYSOBJECTS " + Environment.NewLine +

                       "WHERE TYPE = 'P' " + Environment.NewLine +

                       "AND LEFT(NAME,2) <> 'sp'  " + Environment.NewLine +

                       "AND LEFT(NAME,2) <> 'dt'  " + Environment.NewLine +

                       "AND LEFT(NAME,6) <> 'aspnet' " + Environment.NewLine +

                       "OPEN curGrants " + Environment.NewLine +

                       "FETCH NEXT FROM curGrants INTO @ExecSQL " + Environment.NewLine +

                       "WHILE @@FETCH_STATUS = 0 " + Environment.NewLine +

                       "BEGIN  " + Environment.NewLine +

                       "    Exec(@ExecSQL) " + Environment.NewLine +

                       "FETCH NEXT FROM curGrants INTO @ExecSQL  " + Environment.NewLine +

                       "END " + Environment.NewLine +

                       "CLOSE curGrants " + Environment.NewLine +

                       "DEALLOCATE curGrants ";

       using (SqlConnection connection = new SqlConnection(GetDatabaseConnectionString()))

       {

           connection.Open();

           SqlCommand cmd = new SqlCommand(sql, connection);

           cmd.CommandType = CommandType.Text;

           int i = 0;

           try

           {

               i = cmd.ExecuteNonQuery();

               success = true;

           }

           catch (Exception e)

           {

              //catch your exception here

           }

       }

       return success;

   }

hope that helps everybody.

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Friday, January 12, 2007 12:24 AM by SoftMind

Hello Scott,

You said....

------------------------------------------------

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.

------------------------------------------------

Can you please come again, what exactly you mean by hitting with a browser, what should i click on browser to make it run.

Sorry i have not understood perfectly.

Softmind

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Friday, January 12, 2007 2:09 AM by ScottGu

Hi SoftMind,

Sorry for the confusion. When I said "hit it with the browser" I meant type in the URL to the page within your browser and access it via the browser.  That will cause the page to run and your .SQL file to execute.

Hope this helps,

Scott

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Friday, January 12, 2007 2:28 AM by ScottGu

Hi Liming,

I don't believe you'll need to use this technique if you are using the ASP.NET application services tables if you generate the .SQL file from the Database Publishing Wizard.

But thanks for posting the hint nonetheless - that is a cool technique to keep in mind for other scenarios where you need to change table/sproc permissions!

Thanks,

Scott

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Friday, January 12, 2007 9:20 AM by Wouter van Vugt

To bad you moderate but still answer my question. Perhaps you only let the positives through?

Oh well, off to program Office...

Cya, Wouter

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Friday, January 12, 2007 10:55 AM by ScottGu

Hi Wouter,

I didn't see you comment arrive actually.  When/where did you post it?  Feel free to email it to me as well - sometimes the anti-spam filter on the blog nukes things if there are links.

Note that I post all comments except those that are either: 1) spam, or 2) attack other people without a non-technical basis.

Thanks,

Scott

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Saturday, January 13, 2007 4:51 AM by Dev

Thanks Scott. Just what I was looking for. Microsoft should give you pay rise man. You deserve it ;)

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Saturday, January 13, 2007 8:14 AM by balagh

I am using godaddy hosting and that really did not help unless i did some thing wrong some where. I did get the following security alerts:

An error occured: System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions....

any clue

many thanks

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Sunday, January 14, 2007 4:55 PM by ScottGu

Hi Balagh,

Where are you seeing this error?  I don't think it is coming from the RunSQL.aspx file above - maybe it is a result of something in your application that requires full trust?

Thanks,

Scott

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Thursday, January 18, 2007 4:27 AM by einhandsegler

Hi,

good to know how to upload the database. But more  important for me is, how to download an existing database. I have a CommunityServer Database but no way to get a copy of the database for me. My hoster make a backup every night, but doesn't allow me to download it. I for myself can not make a backup. Using the SQL Management Studio Express it's only possible to create a backup on the server not local. This results for me to not use the MS SQL Server in my projects.

Any idea how to solve this? And by the way the db server my hoster uses is still not an sqlserver 2005...

Thanks,

Michael

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Saturday, February 3, 2007 7:02 AM by Abhay

Thanx alot Mr.ScottGu

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Monday, February 5, 2007 3:27 PM by mzmiric

I really need help with my SQL. At the moment i am building an CMS and i could use some sql installation script with textboxed ui. I already tried some scripts but they were for ASP.net 1.0 so the didn't work. Please help me!! Tnx in adv.

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Tuesday, February 13, 2007 7:39 PM by Mac Kloberg

Thanks Scott. That task had me wondering for a few hours trying to move an app from SQL Express to a production server. This should really be built into Express and come up in the context menu when one right clicks on the main database node in Express (I guess the first place people would for a tool like this).

A wonderful tool though, thanks again!!

Mac

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Friday, February 16, 2007 3:26 PM by ScottGu

Hi MAc,

The good news is that this will be built-in with the express edition of VS "Orcas".  So in the future people won't have to download anything to enable it.

Thanks,

Scott

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Wednesday, February 21, 2007 7:12 PM by David

I fear my last post didn't make it so let's try again.

After complete all the step outlined above successfully I receive the following error message when I attempt to use the database:

EXECUTE permission denied on object 'aspnet_CheckSchemaVersion', database 'GlennCu_ASPNETDB', schema 'dbo'.

It looks similar to what Liming Xu was describing earlier. Would the code that he posted help with this problem and if so, where do I insert it? Be gentle, I'm a neophite.

Thanks

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Friday, February 23, 2007 2:02 AM by ScottGu

Hi David,

Can you send me an email (scottgu@microsoft.com) with more details on this?  Can you also let me know the name of the hoster you are trying this out on?

Thanks,

Scott

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Tuesday, February 27, 2007 11:59 AM by Steve

Scott, I tried this (to copy a database from my hosted SQL server 2000 to a local SQL server express database). It failed because the views are scripted before the tables that are included in the view. So as soon as it got to the first "create view" it failed.

Any advice?

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Saturday, March 3, 2007 5:29 AM by Billy Leo

Hey Scott, I am having the exact same issues as David, I think.

After reading your blog about setting up the aspnetdb database connectionstring, I managed to set the blank DB on my web site's mssql server to the schema of the aspnetdb that ships with VS2005 using the aspnet_regsql.exe.

The thing is that when I try to log in I get the same problem as David...

From my google abilities id figured that this is something to do with not having DBO on the database or something. This confuses me because I can update and add new tables etc... to the db, therefore I should be DBO?

I have read that I need to apply for DBO on my DB from my host. But this is a Saturday and even during the weekdays my host's customer service is poor. Is there any other way of sorting this out?

Thanks,

Great BLog BTW

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Saturday, March 3, 2007 9:17 PM by ScottGu

Hi Billy,

Can you send me an email with more details on this issue?  I will then loop you in with someone on the SQL team to help investigate.

Thx!

Scott

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Saturday, March 3, 2007 10:36 PM by ScottGu

Hi Steve,

Can you send me an email with more details on this issue?  I will then loop you in with someone on the SQL team to help investigate.

Thx!

Scott

# re: Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database

Monday, March 19, 2007 3:17 AM by ScottGu

Hi Matt,

You should be able to just run the RunSQL.aspx page twice (once for each .SQL file).

Can you email me (scottgu@microsoft.com) details of the failure you saw?

Thanks,

Scott