How can I transfer the entire content of several tables to another database, not in the same network?

Question

I have a set of 12 tables, about 4 million rows total, that I need to move four times per year. The database server they are coming from can NOT directly talk to the server that they are moving to. The data has to go via SFTP file transfer.

These 12 tables already exist in the destination database, but can be dropped or truncated as required. The target database will be in 'live' use during the transfer.

In an ideal world, I'd love to have some script that I execute on the source server that tosses all the data into a flat file, and another script on the target server that loads the tables from the flat file.

Options I've seen so far:

• Exporting to CSV (although I'm not sure if having commas in the source data will cause trouble) using the import/export data wizard. I don't like how easy it would be for mistakes to happen over time, for example importing into the wrong table.

• Writing powershell to export the data (CSV files again, I'd love something binary that I don't have to worry about parsing issues).

• Backup and restore the database onto the server, then copy the tables that are needed. This would require copying ~100gb backups across. It would be slow, but would work.

There has to be some tools out there for this sort of work!

@J.D. - Why SFTP? The only approved method to bring data into this network is SFTP. Critical public safety systems, so they are rather picky. Are there other tables? Yes, this is only 12 out of many tables, entire db is roughly 100gb.

Answer 1

There's a command line utility packaged with SQL Server called BCP. It will do what you need.

It supports several file formats. The one you want is "native". This is a proprietary binary format. It will avoid problems with commas, new lines etc.

At the destination use BCP to import into staging tables, one per source table. These have identical schema to the source/ target tables. Then use a partition switch to remove all the existing data and replace it with the newly transferred data.

All this can be wrapped in a few short PowerShell scripts, parameterised, and put into source control.