Exporting data to a remote server with SQL Express

Wed Feb 27, 2008 by Jeff Smith in code-library-c-vb-net, imports-exports

I recently helped a friend out who only had access to SQL Server Management Studio Express, and he needed to copy a database locally from his PC to his remote web hosting company. Normally, the process is a simple backup/restore, but his hosting company does not allow restoring databases. Luckily, however, the company does allow direct access to his hosted database via client tools such as SSMS. Unluckily, SSMS Express does not include any tools that allow you to export data to a remote server.

So, here's what we did: First, we scripted out the entire database, including all tables, indexes, constraints, views, functions, procs, etc, using SSMS Express. This took some time to execute, but it was easy to do.

Then, we ran that script remotely on the hosted database to create the environment. Easy enough.

The final step was potentially the hardest part: Now we had to copy all of the data in all of the tables from the local database to the remote database. I thought about generating INSERT scripts, or even using a tool like MS Access (via linked tables) to get this done, but I decided against them because the INSERT script would be huge (there is a lot of data we are inserting) and any of these methods would require loading data in the exact order needed to satisfy FK constraints. In addition, there was the issue of IDENTITY values and handling that. All could be done, of course, but we were pressed for time and we needed something simple and efficient and that we could easily run again and again as needed.

Then, I remembered my little friend SQLBulkCopy, the really handy .NET 2.0 class that facilitates quickly copying data to a SQL Server database. I had forgotten it includes options to allow for identity values to be specified when bulk inserting, and for FK constraints to be be ignored as well. So, the order that the tables are uploaded in doesn't matter, and identity values are handled fine as well.

A few minutes later, a simple C# console app was written and we were ready to roll:

using System;
using System.Data;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

namespace CopyData
{
    class Program
    {
        static void Main(string[] args)
        {
            string Src="-- source connection string here--";
            string Dest = "-- dest connection string here --";
            int BatchSize = 500;
            int NotifyAfter = 500;

            SqlBulkCopy c = new SqlBulkCopy(Dest, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.TableLock);
            SqlConnection srcConn = new SqlConnection(Src);
            srcConn.Open();

            SqlCommand cm = new SqlCommand("select * from sysobjects where type='u'");
            cm.Connection = srcConn;
            cm.CommandType = CommandType.Text;

            DataTable AllTables = new DataTable();
            SqlDataAdapter a = new SqlDataAdapter(cm);
            a.Fill(AllTables);
            SqlDataReader dr;

            int n = 0;
            int tot = AllTables.Rows.Count;

            Console.WriteLine("Found {0} Tables to copy.", tot);
            Console.WriteLine("");

            c.SqlRowsCopied += new SqlRowsCopiedEventHandler(c_SqlRowsCopied);
            foreach (DataRow r in AllTables.Rows)
            {
                Console.WriteLine("Coping table {0} of {1}: {2}", ++n, tot, r["name"]);
                cm.CommandText = String.Format("select * from [{0}]", r["name"]);
                dr = cm.ExecuteReader();
                c.BatchSize = BatchSize;
                c.DestinationTableName = r["name"].ToString();
                c.NotifyAfter = NotifyAfter;
                c.WriteToServer(dr);
                dr.Close();
            }

            srcConn.Close();
            c.Close();

            Console.WriteLine("");
            Console.WriteLine("Done .. press any key.");
            Console.ReadLine();
        }

        static void c_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            Console.WriteLine(" -- copied {0} rows ...", e.RowsCopied);
        }
    }
}

That's pretty much it... The app runs quicky, gives a nice progress report as it goes, and can easily be modified to use command line arguments or whatever else you need to make it simple and re-usable.

So, if you are using SQL Express to develop an application, and you need to export that database to a hosted web server and a simple backup/restore is not an option, here's all you need to do:

1) Create your destination database
2) Script out all objects in your source database
3) Run the script in your destination database
4) Create the C# console application shown above, enter in your connection strings in the first few lines where indicated, and run it.

That's it! Quick, simple and easy, and really not much harder than a backup/restore would be at all ... in fact, in some ways it is even easier than FTP'ing a backup to your hosted server and dealing with all that.

If you are you using Visual Web Developer to write your application, and therefore don't have the ability to write console applications like the one shown to do your export, you can do this by downloading Visual C# Express.

Legacy Comments


David 2008-02-27	re: Exporting data to a remote server with SQL Express What about the SQL Server Publishing Wizard? http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

Jeff 2008-02-27	re: Exporting data to a remote server with SQL Express I had no idea such a beast existed! I will definitely check it out, thank you David!

Mladen 2008-02-27	re: Exporting data to a remote server with SQL Express see jeff, that's because you haven't fully checked my list of free tools :))

Jeff 2008-02-27	re: Exporting data to a remote server with SQL Express SQL Server Publishing Wizard still doesn't seem to let you publish to an actual SQL database, it is asking for a web service address?

Fred 2008-03-01	re: Exporting data to a remote server with SQL Express I am a newbie SQL Server Express user and have run into this exact problem. When using the SQL Server Publishing Wizard to transfer a local database to a hosted database I run into FK conflicts. I scripted the entire database including data (as there were not that many records in it yet) and when running the script on the remote database kept getting FK conflicts. I would love to know how to simplify the whole process.

S 2008-03-07	re: Exporting data to a remote server with SQL Express > SQL Server Publishing Wizard still doesn't seem > to let you publish to an actual SQL database, > it is asking for a web service address? You may wish to give a try to to the idea described in “Deploying a SQL Database to a Remote Host by using ASP.NET 2.0 callbacks” http://mastering-asp-dot-net.blogspot.com Also I did that as a Windows Forms application + WebBrowser control, implementing two-way communication between client application code and Web page scripting code through the ObjectForScripting and Document properties.

Chris 2008-09-07	re: Exporting data to a remote server with SQL Express Thank you so much this was really helpful. I have been trying to move data from one SQLExpress database to another in a .NET application and looping through a datatable just seemed so slow but was the only way I could get it to work

gopalakrishnan 2009-02-06	Remote database transfer to local database using MySql Hi i want to transfer the database including data's transfer to local database using MySql in asp.net Please help me. Thank you S.Gopalakrishnan Email : gsskkrish@yahoo.co.in

Abhijith 2009-05-18	re: Exporting data to a remote server with SQL Express Awsome example. Thanks for helping the .net developers from getting rid of the huge problem.

Download free new movies 2010-01-22	re: Exporting data to a remote server with SQL Express I scripted the entire database including data (as there were not that many records in it yet) and when running the script on the remote database kept getting FK conflicts. I would love to know how to simplify the whole process.