How to back up an entire database to text files

by James   Last Updated May 15, 2018 20:06 PM

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.

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\'
    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\'
    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\'
    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\'
    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
Tags : sql-server


Related Questions


Linked Server Error on SQL Agent Job

Updated March 16, 2016 08:02 AM






Cache file /home/queryxchang/public_html/apps/frontend/config/../cache/-q-7-206808-how-to-back-up-an-entire-database-to-text-files- could not be written