7

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?

  • 2
    I hear JSON is 1000 nanoseconds faster. – Erik Darling May 15 '18 at 19:20
  • 2
    Anyway, I like this question, but I'm gonna scram before it turns into a PowerShell contest. Good luck! – Erik Darling May 15 '18 at 19:24
  • lol! thanks. I've had this in google drive for a while, wanted to share it. – James May 15 '18 at 19:26
  • 1) And what is your question exactly? 2) "So long as you don’t have any functions, stored procedures, views, constraints or indexes." this does not look like a good DB backup to me... – Patrick Mevzek May 15 '18 at 20:14
  • 4
    @James - when you want to answer your own question, it's totally okay - it's even encouraged. Just break the question and answer up into two parts. I moved your "answer" down to the answers section. – Brent Ozar May 15 '18 at 20:21
2

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.

  • You should add mssql-cli – Kin Shah May 16 '18 at 21:50
  • @Kin Thanks for the suggestion. However, that tool is just a query tool and does not seem to be a viable option for exporting data to csv / tsv files. Unless there is some part of the documentation that I didn't see, but as far as I could tell it's just an interactive query tool. – Solomon Rutzky May 16 '18 at 22:45
  • My bad .. I meant mssql-scripter .. github.com/Microsoft/mssql-scripter .. just like mysqldump utility. – Kin Shah May 17 '18 at 12:13
  • @Kin Ah, ok. I reviewed that project and added the info to my answer. Thanks for mentioning it :-) – Solomon Rutzky May 17 '18 at 14:43
  • For Oracle, SQL*Plus 12c+ added SET MARKUP CSV. Similar for SQLcl. I just don't know if it can do the looping that is required for each table. Loading is SQL*Loader. – Michael Kutz May 17 '18 at 15:54
4

The following is not a good backup solution. Unless you’re quitting your job and feel like pranking your boss on the way out the door, you shouldn’t use it. But I learned a few things and had fun writing it, hopefully other people will, too. We are going to use some ETL concepts that might actually be useful in other circumstances. The full scripts are at the bottom. Here’s what we are going to do:

Write the Schema to its own table
Write each table in the database to its own dynamically named text file
Re-create the Schema to a new DB from the text file
Re-create & Bulk Insert each table from its text file

Before we begin: To do the whole DB, you will need several hundred GB of storage. If you’re too chicken, you can toss in ‘top 1000’s on the script to limit the size of the .txt files, then you won’t have to explain to your boss how you filled up C:\

Enable xp_cmdshell - explained here:

https://blog.sqlauthority.com/2007/04/26/sql-server-enable-xp_cmdshell-using-sp_configure/

  1. Write the schema to a text file

We are going to check and see if the table already exists (If we’ve run the script before). It’s easier to just drop the table every time.

    if exists (select name from sys.tables st with (Nolock) where name = 'HeaderTable')

    begin
    drop table HeaderTable
    end

    SELECT
        st.name, sc.name 'Column_Name', t.Name 'Data_type',sc.max_length 'Max_Length',
        sc.precision, sc.scale, sc.is_nullable
        into HeaderTable
        FROM    
        sys.tables st
        inner join sys.columns sc on sc.object_id = st.object_id
        INNER JOIN sys.types t ON sc.user_type_id = t.user_type_id

Now, if you do a quick select * from HeaderTable, you’ll see every table, every column, its data type and max_length - everything we will need to recreate it.

  1. Write each table in the database to its own dynamically named text file

Here’s where it starts to get cool. We are going to use a cursor to loop through sys.tables and dump each one into its own .txt.

We are going to use a bunch of variables:

@table is going into the cursor. It will hold the names of each table as we go.

@Database, @filepath, @filename, @filetype are all going to be used to build a set of dynamic SQL statements.

@sql is going to hold our final SQL commands, to put to sp_executesql.

Things get a bit tricky with the delimiters and row terminators. If you use the defaults of | and /r, you’re going to have a real hard time with the Comments table. We are going to have to use something that we know isn’t used anywhere in the StackOverflow database. You could use newid(), rocket ships & googly eyes or you could use your favorite nursery rhyme. Anything, so long as it isn’t on StackOverflow already.

Here’s the script:

    declare @table varchar(255),
    @Database varchar(255),
    @filepath varchar(255),
    @filename varchar(255),
    @filetype varchar(255),
    @sql nvarchar(max),
    @delimiter varchar(255),
    @rowterminator varchar(255)

    set @Database = 'StackOverflow'
    set @filepath = 'C:\Data\' -- fix pretty-print mishandling of not-truly escaped ' 
    set @filetype = '.txt'
    set @delimiter = 'WhimmyWhammyWozzle'
    set @rowterminator = 'WubaLubaDubDub'

    declare c cursor local for

    select name from sys.tables with (Nolock)

    open c

    fetch from c into @table

    while @@FETCH_STATUS = 0
    begin

    SET @filename = @table

    --output to txt
    set @sql = N'declare @bcp varchar(4000)
    set @bcp = ''bcp " select top 10000 * from ' + @table + ' " queryout '
    + @filepath +  @filename + @filetype + ' -t "' + @delimiter + '" -r "'
     + @rowterminator + '" -c -T -d ' + @Database + '''
    print @bcp

    EXECUTE master.dbo.xp_cmdshell @BCP'

    print @sql
    --exec sp_executesql @sql

    fetch next from c into @table
    end

    close c

    deallocate c

Note that I’ve put the safety on in case you just pasted it in and hit F5. Not that anyone would ever do that, right? exec sp_executesql @sql won’t run until you remove the commenters. I’ve also included a top 10000.

Go to your file path, and you should see a bunch of text files.

Go ahead and open one up and change some data. If you think opening them manually is for peasants, you can use Fart.exe to find and replace in all of the text files.

  1. Re-Write the Header

Go ahead and create a new database.

We are going to hard code the recreation of the header table, and use it to rebuild the rest.

restore header:

    if exists (select name from sys.tables where name = 'HeaderTable')
    begin
    drop table HeaderTable
    end

    create table HeaderTable
    (Table_Name varchar(255),
    Column_Name  varchar(255),
    Data_type  varchar(255),
    Max_Length  varchar(255),
    precision  varchar(255),
    scale varchar(255),
    is_nullable  varchar(255))

And now, we are going to bulk insert our Schema into HeaderTable:

    set @sql = 'BULK INSERT HeaderTable FROM ''' + @filepath + 'HeaderTable'+ @filetype + ''' WITH (FIELDTERMINATOR = '''
     + @delimiter + ''', ROWTERMINATOR = ''' + @rowterminator + ''')'
    print @sql
    --exec sp_executesql @sql

    We will have to tidy it up a bit, to make the next steps easier:

    update HeaderTable
    set Max_Length = 'max'
    where Max_length = -1

    update HeaderTable
    set Max_Length = '(' + Max_Length + ')'

    update HeaderTable
    set Max_Length = ''
    where Data_type in ( 'int', 'bigint', 'smallint', 'tinyint',
    'date','datetime', 'uniqueidentifier', 'sysname', 'bit')
  1. Re-create & Bulk Insert each table from its text file

And here’s where things get cool again. We are going to loop through HeaderTable and re-create each table, concatenating the Create statement with STUFF(). Don’t ask me how stuff() works - an old coworker (Mike Ignatoski) gave this to me years ago. Reliable sources say he originally got it from some guy named Solomon.

    declare @table varchar(255),
    @column_string nvarchar(max),
    @sql nvarchar(max),
    @string nvarchar(max),
    @filepath varchar(255),
    @filename varchar(255),
    @filetype varchar(255),
    @sql nvarchar(max),
    @delimiter varchar(255),
    @rowterminator varchar(255)


    set @filepath = 'C:\Data\' -- fix pretty-print mishandling of not-truly escaped '
    set @filetype = '.txt'
    set @delimiter = 'WhimmyWhammyWozzle'
    set @rowterminator = 'WubaLubaDubDub'

    declare c cursor local for

    select distinct Table_Name from HeaderTable
    where Table_Name != 'HeaderTable'

    open c
    fetch from c into @table

    while @@FETCH_STATUS = 0
    begin

    set @string = null

    set @string = (select stuff( (
    select ', ' + Column_Name + ' ' + Data_type  + Max_Length from HeaderTable
    where Table_Name = @table
    for xml path ('')),1,2,''))

    print @string

    set @sql =  ' if not exists (select top 1 name from sys.tables where name = ''' + @table + ''') begin
    create table ' + @table + ' (' + @string + ') end'

    print @sql
    exec sp_executesql @sql

    --populate the table
    set @sql = 'BULK INSERT ' + @table + ' FROM ''' + @filepath + @table + '.txt'' WITH (FIELDTERMINATOR = '''
    + @delimiter  + ''', ROWTERMINATOR = ''' + @rowterminator + ''' )'

    print @sql
    exec sp_executesql @sql

    fetch next from c into @table

    end

    close c

    deallocate c

And there you have it - your database has been restored from text files. You can consign the .bak file to the garbage pile of history! So long as you don’t have any functions, stored procedures, views, constraints or indexes.

Here are the full scripts: Bad Idea Jeans Backup:

    declare @table varchar(255),
    @Database varchar(255),
    @filepath varchar(255),
    @filename varchar(255),
    @filetype varchar(255),
    @sql nvarchar(max),
    @delimiter varchar(255),
    @rowterminator varchar(255)

    set @Database = 'StackOverflow'
    set @filepath = 'C:\Data\' -- fix pretty-print mishandling of not-truly escaped '
    set @filetype = '.txt'
    set @delimiter = 'WhimmyWhammyWozzle'
    set @rowterminator = 'WubaLubaDubDub'

    --create database header

    if exists (select name from sys.tables st with (Nolock) where name = 'HeaderTable')

    begin
    drop table HeaderTable
    end

    SELECT
        st.name, sc.name 'Column_Name', t.Name 'Data_type',sc.max_length 'Max_Length',
        sc.precision, sc.scale, sc.is_nullable
        into HeaderTable
        FROM    
        sys.tables st
        inner join sys.columns sc on sc.object_id = st.object_id
        INNER JOIN sys.types t ON sc.user_type_id = t.user_type_id

        select * from HeaderTable

    declare c cursor local for

    select name from sys.tables so with (Nolock)

    open c

    fetch from c into @table

    while @@FETCH_STATUS = 0
    begin

    SET @filename = @table


    --output to txt
    set @sql = N'declare @bcp varchar(4000)
    set @bcp = ''bcp " select top 10000 * from ' + @table + ' " queryout '
    + @filepath +  @filename + @filetype + ' -t "' + @delimiter + '" -r "'
     + @rowterminator + '" -c -T -d ' + @Database + '''
    print @bcp

    EXECUTE master.dbo.xp_cmdshell @BCP'

    print @sql
     exec sp_executesql @sql

    fetch next from c into @table
    end

    close c

    deallocate c

Bad Idea Jeans Restore:

declare @table varchar(255),
    @column_string nvarchar(max),
    @sql nvarchar(max),
    @string nvarchar(max),
    @filepath varchar(255),
    @filename varchar(255),
    @filetype varchar(255),
    @delimiter varchar(255),
    @rowterminator varchar(255)


    set @filepath = 'C:\Data\' -- fix pretty-print mishandling of not-truly escaped '
    set @filetype = '.txt'
    set @delimiter = 'WhimmyWhammyWozzle'
    set @rowterminator = 'WubaLubaDubDub'

    --restore header
    if exists (select name from sys.tables where name = 'HeaderTable')
    begin
    drop table HeaderTable
    end

    create table HeaderTable
    (Table_Name varchar(255),
    Column_Name  varchar(255),
    Data_type  varchar(255),
    Max_Length  varchar(255),
    precision  varchar(255),
    scale varchar(255),
    is_nullable  varchar(255))

    set @sql = 'BULK INSERT HeaderTable FROM ''' + @filepath + 'HeaderTable'+ @filetype + ''' WITH (FIELDTERMINATOR = '''
     + @delimiter + ''', ROWTERMINATOR = ''' + @rowterminator + ''')'
    print @sql
    exec sp_executesql @sql

    --make some changes so that we can concatenate our create tables more easily
    update HeaderTable
    set Max_Length = 'max'
    where Max_length = -1

    update HeaderTable
    set Max_Length = '(' + Max_Length + ')'

    update HeaderTable
    set Max_Length = ''
    where Data_type in ( 'int', 'bigint', 'smallint', 'tinyint',
    'date','datetime', 'uniqueidentifier', 'sysname', 'bit')

    select * from HeaderTable

    --restore DB

    declare c cursor local for

    select distinct name from sys.columns
    where name != 'HeaderTable'

    open c
    fetch from c into @table

    while @@FETCH_STATUS = 0
    begin

    set @string = null

    set @string = (select stuff( (
    select ', ' + Column_Name + ' ' + Data_type  + Max_Length from HeaderTable
    where name = @table
    for xml path ('')),1,2,''))

    print @string

    set @sql =  ' if not exists (select top 1 name from sys.tables where name = ''' + @table + ''') begin
    create table ' + @table + ' (' + @string + ') end'

    print @sql
    --exec sp_executesql @sql

    set @sql = 'BULK INSERT ' + @table + ' FROM ' + '' + @filepath + @table + '.txt'' WITH (FIELDTERMINATOR = '''
    + @delimiter  + ''', ROWTERMINATOR = ''' + @rowterminator + ''' )'

    print @sql
    --exec sp_executesql @sql

    fetch next from c into @table

    end

    close c

    deallocate c
  • 1
    While this does start with "This isn't a good solution", more warning is needed in case someone tries to use this code due to the following issues: 1) doesn't properly handle many datatypes: DECIMAL, SQL_VARIANT, XML, CLR, etc; 2) doesn't handle Schemas; 3) doesn't set collation; 4) doesn't handle computed columns; 5) doesn't handle columns options: ROWGUID, SPARSE, IDENTITY, etc; 6) possible string data loss from forcing all code pages and Unicode into system default code page; 8) Major potential bug: tables can be recreated with columns in different order than in original table; 9) more? – Solomon Rutzky May 16 '18 at 0:05

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.