backup a specific set of tables to be moved to a remote system [duplicate]

Posted on

Question :

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.

Answer :

Leave a Reply

Your email address will not be published. Required fields are marked *