Best method to copying multiple tables from one database to another database on a seperate SQL Server using VB.net

Question

I'll give everyone a quick rundown on what I'm attempting here.
So I've got 2 servers, one offsite and the other onsite.

My goal is to create an app in VB that will allow the user to select what database they need to be copied down to the opposite server. I've been floating some ideas around on whether to use the SqlBulkCopy method or Transact-SQL statements, but so far either one has been a much more difficult task than anticipated.

So far what I have is a SQL statement creating a blank database on the opposite server, but am getting stuck when trying to copy all the tables within that specific database to the newly created database onto the offsite server.

KEEP IN MIND: I'm attempting to copy ALL the tables within the specified database and would need to keep the same table schema, and all the records within those tables.

The number of tables and names of tables differs from database to database So I'm not able to just simply create the tables using SQL statements.

My question is: what would the best method be to accomplishing this goal?
Is there a way to use SqlBulkCopy that copies all the tables within a specified database or is there another way that I haven't mentioned that would work even better?

Answers are greatly appreciated! Thanks!!

Answer 1

If you MS Access you can connect to 'external database(s)' and simple select from one table / database into the other table in the other database

Answer 2

UPDATE

Based on your latest comment, you can always use a SqlCommand object and use a command along the lines of

MySQLCommand.CommandText = String.Format("BACKUP DATABASE {0} TO DISK='{1}'", dbName, location)

It would be similar for the restore command:

MySQLCommand.CommandText = String.Format("RESTORE DATABASE {0} FROM DISK='{1}'", dbName, location)

Original Answer

If it doesn't need to be a VB.Net application (ie don't reinvent the wheel), there is a free tool called SQL Server Management Studio Express that you can download an install on each server. Simply connect to your source server, select the database you want and select to do a backup to file. Then you can copy that to a USB stick or whatever you prefer and take it to the destination server and run a restore using the same tool. I do this often and it takes very little time for a decent sized database (approx 1 GB as a compressed backup takes around 1 minute to backup and another minute to restore).

NOTE: The link points to SQL Server 2012 Management Studio Express. You may need a different version depending on your version of SQL Server.

• Backup documentation on MSDN
• Restore documentation on MSDN

I have the full version of 2012, but here is a screenshot of the basic idea (simple context menus via right-clicking and dialogs drive the entire process).

This can of course all be done via scripts as well (and if you are determined to write a VB.NET application, you could simply call the scripts through a .Net Process).

Answer 3

1. Download trial version of SQL compare,
2. Synchronize the remote db with your local db
3. Download trial version of SQL Data Compare
4. Sync data from Local to remote.

The tools have a free 14 day trial period.