Moving data between Microsoft SQL Server Databases

We have received some questions on how to best move data to and from AppHarbor hosted SQL Server databases and this is an issue we have been struggling with ourselves. Users running MySQL databases can use mysqldump, but there is no good SQL Server equivalent for large databases (bcp.exe only copies one table at a time).

For databases with limited data, SQL Server can script both database schema and data using SQL Server Management Studio. For databases with tables with many rows, the generated scripts grow too large to manage.

To handle those cases, we have written a simple console application that copies data between SQL Server databases. The app uses SQL Server Management Objects to retrieve table information and SqlBulkCopy to copy the actual data.

Here's an example of copying data from an AppHarbor database to a local database: AppHarbor.SqlServerBulkCopy.exe --srcserver=db000.appharbor.net --srcusername=dbxx --srcpassword=srcpassword --srcdatabasename=dbxx --dstserver=.\SQLEXPRESSADV --dstusername=dbyy --dstpassword=dstpassword --dstdatabasename=dbyy --ignoretables=TableToIgnore

It's currently up to you to make sure that the database schemas match. The app currently also only supports SQL Server authentication.

To get started, grab the code on GitHub, build it, and start copying. For optimal performance, you should run the app on the machine that houses the database you are copying to or from. The app has been tested with AppHarbor but should work with any pair of recent Microsoft SQL Server databases.