Sql-server – backup a specific set of tables to be moved to a remote system


I need to generate either backups or data dumps for about 30 tables. I want to generate a file of some sort that contains the data for these tables and transfer them to a different system. Each table has 3.8-4.5 million rows. also a large number of these rows have columns with text that has newline characters which need to be handled correctly.

I'm using Sql Server 2012 and have SQL Server Management Studio.

Due to security and other particular reasons I cannot just use SQL Server Managment Studio's Import/Export Wizard for simply exporting tables from one database to another. I have a requirement to get a dump of the data to transfer to a remote system.

I have tried SQL Server Management Studio's Generate Scripts capability, which does appear to handle the newline characters, however There is another issue. In some of the fields the text is similar to "something blah \". that slash causes a problem with the script that gets generated, because the Generate Scripts process wraps that text in '' and with \' it basically escapes the single quote which corrupts the remaining data.

I have also tried to generate flat files via SQL Server Management Studio's Import/Export wizard and this doesn't appear to handle the newline characters correctly and again corrupting the remaining data.

Any help would be greatly appreciated. and I can clarify any information that is missing.

Best Answer

Just take a full backup (with compression, if possible), restore it on the other server, and then you can select into or insert/select without having to worry about what happens to carriage returns, line feeds, and other characters that cause problems for files. You want to move (parts of) a database, not files.

Trying to just extract some 120 million rows from a database sounds like more trouble than it's worth. You're making this harder than it should be, for what? To save a few bytes in data transfer?