2

I'm looking to export all tables from a SQL Server Standard Edition database with data into CSV files onto a remote linux server. I setup FreeTDS and am able to connect, do SQLCMD or bcp successfully.

I know we can use SQLCMD but I was wondering if there is a way that we can write each table into its individual CSV file using a single connection instead of looping through every table in the database and writing to a CSV file.

Any thoughts if its possible?

Thanks in advance, simak

  • Just out of curiosity, what is wrong with looping through a list of DBs in the CMD script? – Solomon Rutzky Jan 14 '15 at 18:35
  • Thanks for responding. Want to avoid multiple hits to the server that get opened For every DB, that gets passed into SQLCMD, – Bee Jan 14 '15 at 18:53
  • How many tables do you have? Unless we are talking 1000+ tables I am not sure the overhead is all that much. – Solomon Rutzky Jan 14 '15 at 20:12
  • 1
    You're dumping an entire database into CSV files over a potentially slow network connection with probably slow disks at the other end, and you're concerned about the overhead of multiple hits to the server? I think you need to focus better on the actual problem. – Aaron Bertrand Jan 14 '15 at 22:23
  • 1
    @srutzky sure, that's what YOU would do, but that's not what the OP described, and doesn't seem to be the part they want to optimize. – Aaron Bertrand Jan 15 '15 at 12:26
2

I looked into doing this years ago and looked into both BCP and SSIS. Neither one really provided for great flexibility. BCP needed format files to get the text-qualifiers, etc. But this was for a dynamic backup solution for a subset of tables to get archived nightly. SSIS seemed a bit more dynamic (though not fully), but couldn't do a few of the things that BCP could. I needed something that could dynamically accept changes to tables (include new tables, don't require removed tables) AND changes to columns (as I needed to do "SELECT * FROM {SchemaName}.{TableName};") AND dynamic text-qualification AND column headers, etc.

If you have a static set of tables, then SSIS, using a ForEach container, would be one way to do this (again, assuming the desire is to stay away from BCP due to requiring multiple calls).

Using PowerShell would probably also be an option that would allow for more flexibility than SSIS.

However, if you need something fully dynamic and/or need to stick with a single connection per each DB (all tables in that DB) or even a single connection per server (all tables for all databases), then the only way I have ever found to handle this is the one that I created. It is a SQLCLR stored procedure called DB_BulkExport which has parameters for @Query and @FilePath that allow for easily cycling through a list of all databases, then per each one cycling through a list of tables, and building both the query and filename from those pieces. You could write a fairly simple stored proc that uses a cursor to get the Database and Table names and call this proc, hence being a single call to SQLCMD.EXE to call that one proc. Or it can be scheduled via SQL Agent without needing to enable xp_cmdshell.

This procedure -- DB_BulkExport -- is part of the SQL# library (which I am the creator of), though it is only available in the Full (i.e. paid for) version, not in the Free version. I generally do not mention stuff here that is not available in the Free version, but as I said, I am not aware of any other existing means of automating this type of process. Of course, anyone is free to write this themselves, either as a SQLCLR proc as I have done, or as a stand-alone .NET app, or using PowerShell. It is more than a few lines of code in C# and I suspect it is also not overly simplistic in PowerShell either (but I haven't tried and don't have enough experience with PowerShell to really know).


EDIT:
Regarding how I have used the above mentioned stored proc in at least one instance: It was used to export data nightly, for long-term archival, the oldest day from a large table that was itself a common, short-term archival of data from 20 source servers.

  • It would extract maybe 10 million rows for a given date
  • The table held 150 - 450 million rows (fluctuation due to several factors)
  • It would grab all records for the oldest date and export them to a delimited file
  • 1 day was 5 million rows (avg)
  • Each row was 1240 bytes (avg; 2 URLs plus a few other text fields, etc)
  • The file was 3 GB - 6 GB depending on the day
  • It exported to a local temp folder so that it didn't need to go across the network.
  • The archival process then:

    • used the File_GZip function -- which can handle zip64 (i.e. source files over 4 GB) -- to compress the text file down to 1 GB - 2 GB (no, it didn't take much memory; buffer is 8k)
    • used File_Copy to copy the .gz file to a networked archive folder
    • used File_Delete to remove the local file(s)
    • If the above steps completed successfully, the date of the data that was just archived was placed into a queue table for another SQL Agent job to pick up and incrementally (in small batches as the table was constantly being filled from the 20 source servers) delete the records from the archive table.

      So to amend the description above of the archival process: it would extract the oldest date that was not in this queue table.

    • Once all of the records had been deleted the date being deleted would be removed from the queue table.

Ideally there would be a local drive that would not have pagefile, mdf, or ldf files on it that could be used for the extraction and compression. For the long-term archival process noted above, that server was a dedicated archive that just collected data for a few tables from the 20 source servers. Hence the only querying of the data 99% of the time was the SELECT * FROM ArchiveTable to do the extract; the other 1% of the time it was research.

|improve this answer|||||
  • Hello Srutzky, Thank you very much for your response. The proc does indeed seem promising and may suit our needs. But ofcourse we will have to look further and see it will be scalable for our environment. (600+ databases). – Bee Jan 15 '15 at 1:14
  • @simak The only way I can think to get the data any faster would be to take compressed, native backups. I am not sure why you are wanting to extract to CSV, but if there is a specific reason, then I am not aware of a faster method. In fact, with 600+ DBs, processing all of them and all of their tables in a single proc call would seem to be the most scalable as it has the least overhead. – Solomon Rutzky Jan 15 '15 at 3:23
  • @simak How many tables per database? Is it consistent or do you at least have an average? I assume you meant actual databases (and not tables) in your comment above regarding "600+ databases". Can you maybe please put the stats in the question so the context of what you want and why you want it are clearer? Thanks. – Solomon Rutzky Jan 15 '15 at 18:13
  • Unfortunately, the number of tables is not consistent across the 600 DBs. On an average we are looking at 300 tables in a DB. With the standard edition and our environment, we are kind of limited in the way we can capture changes from all DBs into a File System. So, thinking of using a replicating tool to write to one DB and then export to a flat file..I know a convoluted approach, but unless we upgrade all 600 to EE, not sure if we have better and more scalable options. – Bee Jan 16 '15 at 21:13
  • @simak I think it would help if you explained what you are trying to accomplish as opposed to starting out with the "how" you want to accomplish it (i.e. what we have been talking about so far, if I am understanding correctly). What changes are you wanting to capture? Any data to any table? Are you saying you are thinking of having all 180,000 tables write their DML statements statements to a single server? Also, you were originally asking about exporting the entire table, but now you are just wanting changes? Is that correct? What is the intent of logging this on the File System? – Solomon Rutzky Jan 16 '15 at 21:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.