SQL Server Backup – How to Back Up Entire Database to Text Files

backupexportscriptingsql server

If it’s stupid and it works, it’s still stupid.

The hullabaloo about GDPR got me thinking – if you had to go into your backups and modify/delete data, how would you do it? Cracking open a .bak file sounds hard to me. But it’s pretty easy to open up a .txt file.

Could I back up my database to a text file instead, thereby making it easier to delete history out of the backups?

Best Answer

While this is not a good choice for actual backups, there are some use cases for dumping tables to text files. In that context, an alternative approach to using xp_cmdshell to call BCP.EXE is to use the DB_BulkExport SQLCLR Stored Procedure that is available in SQL# (which I wrote). You can then use File_GZip to compress the files.

The following are advantages and disadvantages to using DB_BulkExport compared to BCP.EXE:

Pros:

  1. No need to enable xp_cmdshell
  2. Adds column headers
  3. Text-qualifies either no fields, all fields, or only fields that require it (i.e. non-numeric / non-binary fields). Text-qualifying string fields means that you can use a standard field delimiter (such as a comma or tab), and standard row delimiter (such as CRLF or LF / NL), instead of a character or sequence of characters that you hope are not present in any string fields. The text qualifier is user-defined (typically double-quote), and the escape sequence for embedded text qualifiers can be anything you like (typically also double-quote, but could also be back-slash).
  4. Control over BIT representation: 1 / 0, T / F, or True / False.
  5. (soon) Control over SMALLDATETIME, DATETIME, DATE, TIME, DATETIME2, and DATETIMEOFFSET format.

Cons:

  1. Not free (DB_BulkExport and File_GZip are only available in the Full version)

Please note that DB_BulkExport only exports data; it does not record the structure of the result set. This is no different than BCP, nor is it really any different than the solution provided in the main community wiki answer given that the solution there ignores collations, computed columns, most column options, etc.

Another option for SQL Server, mentioned by Kin, is mssql-scripter. This is a free / open source, multi-platform, Python-based tool. I have not used it, but it seems to export DDL and/or DML statements that can be executed to recreate the data and/or schema (tables, constraints, etc). It appears to only export the data as INSERT statements instead of as delimited fields. Looks quite interesting, but please review the "Issues" to make sure that there isn't anything that would impact your usage.

Also, even though this question (and the main community wiki answer) mention SQL Server, the issues surrounding GDPR are not specific to SQL Server. So, just thought I would mention that the ability to export tables (and even schema, etc) is available for MySQL in the following two utilities that come with it:

The same can be done in PostgreSQL using the following two utilities that come with it:

For Oracle, please see the following resources, which I believe will at least get you very close, if not fully equivalent output (thanks to Michael Kutz for pointing me in the right direction):

I'm not sure if similar utilities come with DB2.

Related Question