Backup Your SQL Data Into A Script

The objective: Backup your SQL Server data into a script.

Tools of sorts can do this trivial task easy. One of the tools that can is MyGeneration. I'm not here to talk about this nifty tool though. I'm here to show you a nice TSQL script that can do that. Lo and behold.

SET NOCOUNT ON

-- create a temporary table to hold the table info of the current db
DECLARE @tables TABLE (
seq int identity(1,1)
,id int
,name varchar(100))

INSERT INTO @tables
SELECT s.id, s.name
FROM sysobjects s
WHERE s.xtype='U'
AND s.status > 0
ORDER BY s.name

-- create a temporary table to hold the columns of the table being process
DECLARE @columns TABLE (
name varchar(100))

-- drop #temp table if it exists, this may happen if the running this sp previously was cancelled
-- or returns an exception
IF object_id('tempdb..#temp', 'U') IS NOT NULL
DROP TABLE #temp

-- create a temporary table to hold the constructed INSERT commands
CREATE TABLE #temp (
seq int identity(1,1)
,cmd varchar(8000))

-- holds the sequence numbers
DECLARE @tabseq int
DECLARE @cmdseq int

-- holds table info
DECLARE @tablename varchar(100)
DECLARE @tableid int

-- usually "dbo"
DECLARE @user_name varchar(100)
SET @user_name = user_name()

-- holds the SELECT statement that's ran against the table to create the INSERT commands (@cmd)
DECLARE @select varchar(8000)
-- holds the INSERT command
DECLARE @cmd varchar(8000)
-- holds the number of INSERT command for the table in process
DECLARE @cmd_count int
-- holds the field part of the constructed INSERT command
DECLARE @fields varchar(8000)
-- holds the VALUES part of the constructed INSERT command
DECLARE @values varchar(8000)

-- START SCRIPT CONSTRUCTION
PRINT 'SET NOCOUNT ON'
PRINT ''
PRINT 'USE ' + db_name()
PRINT ''

-- process each table
WHILE EXISTS(SELECT TOP 1 * FROM @tables)
BEGIN
-- reset these values for each table
SET @fields = ''
SET @select = ''
SET @values = ''

SELECT TOP 1
@tabseq = seq,
@tableid = id,
@tablename = name
FROM @tables

-- fetch the column info for the current table
INSERT INTO @columns
SELECT name
FROM syscolumns
WHERE id = @tableid
ORDER BY colid

-- construct @fields and @values string for each columns,
-- this have an effect of looping through the @columns table
UPDATE @columns
SET @fields = @fields + name + ', '
,@values = @values + 'ISNULL('''''''' + CAST(' + name + ' AS varchar(8000)) + '''''''',''NULL'') + '', '' + '

SET @select = 'SELECT ''INSERT INTO ' + @user_name + '.' + @tablename+ ' (' + LEFT(@fields, LEN(@fields) - 1) + ') VALUES ('' + ' + LEFT(@values, LEN(@values) - 9) + ' + '')'' FROM ' + @tablename

INSERT INTO #temp EXEC (@select)

-- if @@ROWCOUNT return zero that means the SELECT statement above did not return any data
-- which means the table is empty and therefore need not be scripted
IF @@ROWCOUNT > 0
BEGIN
-- disable constraints before inserting data
PRINT '-- Data population script for ' + @tablename + ' table.'
PRINT 'SET IDENTITY_INSERT ' + @tablename + ' ON'
PRINT 'ALTER TABLE ' + @tablename + ' NOCHECK CONSTRAINT ALL'
PRINT ''

SELECT @cmd_count = COUNT(*) FROM #temp

-- construct INSERTS
PRINT '-- Data population script for ' + @tablename + ' table (' + CAST(@cmd_count AS varchar(100)) + ' records).'
WHILE EXISTS(SELECT TOP 1 * FROM #temp)
BEGIN
SELECT TOP 1 @cmdseq = seq, @cmd = cmd FROM #temp

PRINT @cmd

DELETE #temp WHERE seq = @cmdseq
END

-- enable constraints back
PRINT ''
PRINT 'ALTER TABLE ' + @tablename + ' CHECK CONSTRAINT ALL'
PRINT 'SET IDENTITY_INSERT ' + @tablename + ' OFF'
PRINT ''
END

DELETE #temp
-- clear @columns table
DELETE @columns
DELETE @tables WHERE seq = @tabseq
END

PRINT 'SET NOCOUNT OFF'

-- END SCRIPT CONSTRUCTION

DROP TABLE #temp

Now, there's not much to say about this script. Comments are there to do just that. Just run this inside Query Analyzer within a database context and you're on. The result can be seen inside the Message pane that you could copy and paste into a file or just click on the Query > Results To File menu to have Query Analyzer prompt you a file where the result would be directly save when you run the script (see Figures 1 & 2)

Figure 1.

Figure 2.

Be forewarned though, this script has limits to what data type can be scripted, for one, it cannot do image data types and the likes, don't ask me what "the likes" are I haven't got time to test them =) all but it works for most common data types i.e. int, varchar, text, char, and all their mutations. It also only works for SQL Server 7.0 and later.

Cheers!

ERRATA:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1053&lngWId=5

I just found out that the script posted here does not work for tables without an identity so I corrected that version to conditionally "SET IDENTITY INSERT...". This second version also tries to include table with images although not populating that column.