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.
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.