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.