HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to back up an entire database to text files

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
scriptingsqlsql-serverstackoverflowexportdbabackup
entiretextdatabasebackfileshow

Problem

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?

Solution

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/

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

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

  • 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 + 'HeaderTab

Code Snippets

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

    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')
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

Context

StackExchange Database Administrators Q#206808, answer score: 4

Revisions (0)

No revisions yet.