How to back up an entire database to text files
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 + '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')
- 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
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:
- No need to enable
xp_cmdshell - Adds column headers
- 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).
- Control over
BITrepresentation: 1 / 0, T / F, or True / False. - (soon) Control over
SMALLDATETIME,DATETIME,DATE,TIME,DATETIME2, andDATETIMEOFFSETformat.
Cons:
- 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:
- mysqldump
- mysqlpump
The same can be done in PostgreSQL using the following two utilities that come with it:
- pg_dump
- pg_dumpall
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):
- How do I spool to a CSV formatted file using SQLPLUS?
- Fast Generation of CSV and JSON from Oracle Database
- Creating CSV Reports
- Automate the generation of SQL query output to CSV
I'm not sure if similar utilities come with DB2.