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.