MSSQL Table Backup Script

This is a quick way to backup table structure and data, this will go out to the system table and query all tables then provide a script to recreate the tables.

SELECT 'create table [' + so.NAME + '] (' + o.list + ')' + CASE
		WHEN tc.Constraint_Name IS NULL
			THEN ''
		ELSE 'ALTER TABLE ' + so.NAME + ' ADD CONSTRAINT ' + tc.Constraint_Name + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List) - 1) + ')'
		END
FROM sysobjects so
CROSS APPLY (
	SELECT '  [' + column_name + '] ' + data_type + CASE data_type
			WHEN 'sql_variant'
				THEN ''
			WHEN 'text'
				THEN ''
			WHEN 'ntext'
				THEN ''
			WHEN 'xml'
				THEN ''
			WHEN 'decimal'
				THEN '(' + cast(numeric_precision AS VARCHAR) + ', ' + cast(numeric_scale AS VARCHAR) + ')'
			ELSE coalesce('(' + CASE
						WHEN character_maximum_length = - 1
							THEN 'MAX'
						ELSE cast(character_maximum_length AS VARCHAR)
						END + ')', '')
			END + ' ' + CASE
			WHEN EXISTS (
					SELECT id
					FROM syscolumns
					WHERE object_name(id) = so.NAME
						AND NAME = column_name
						AND columnproperty(id, NAME, 'IsIdentity') = 1
					)
				THEN 'IDENTITY(' + cast(ident_seed(so.NAME) AS VARCHAR) + ',' + cast(ident_incr(so.NAME) AS VARCHAR) + ')'
			ELSE ''
			END + ' ' + (
			CASE
				WHEN IS_NULLABLE = 'No'
					THEN 'NOT '
				ELSE ''
				END
			) + 'NULL ' + CASE
			WHEN information_schema.columns.COLUMN_DEFAULT IS NOT NULL
				THEN 'DEFAULT ' + information_schema.columns.COLUMN_DEFAULT
			ELSE ''
			END + ', '
	FROM information_schema.columns
	WHERE table_name = so.NAME
	ORDER BY ordinal_position
	FOR XML PATH('')
	) o(list)
LEFT JOIN information_schema.table_constraints tc ON tc.Table_name = so.NAME
	AND tc.Constraint_Type = 'PRIMARY KEY'
CROSS APPLY (
	SELECT '[' + Column_Name + '], '
	FROM information_schema.key_column_usage kcu
	WHERE kcu.Constraint_Name = tc.Constraint_Name
	ORDER BY ORDINAL_POSITION
	FOR XML PATH('')
	) j(list)
WHERE xtype = 'U'
	AND NAME NOT IN ('dtproperties')
ORDER BY so.NAME

This is a bit of a hack but works to backup all the rows in your tables. Run the query and it will generate the proper syntax to run the bcp Utility.

SET NOCOUNT ON

DECLARE @AllTables TABLE (
	DBName NVARCHAR(200)
	,SchemaName NVARCHAR(200)
	,TableName NVARCHAR(200)
	)
DECLARE @SearchDB NVARCHAR(200)
	,@SearchS NVARCHAR(200)
	,@SearchTbl NVARCHAR(200)
	,@SQL NVARCHAR(4000)

SET @SearchDB = NULL --Search for DB, NULL for all Databases
SET @SearchS = NULL --Search for Schemas, NULL for all Schemas
SET @SearchTbl = NULL --Search for Tables, NULL for all Tables
SET @SQL = 'SELECT
        ''?''
        ,s.name
        ,t.name
         FROM [?].sys.tables t
         JOIN sys.schemas s on t.schema_id=s.schema_id
         WHERE  ''?'' LIKE ''%' + ISNULL(@SearchDB, '') + '%''
         AND s.name LIKE ''%' + ISNULL(@SearchS, '') + '%''
         AND t.name LIKE ''%' + ISNULL(@SearchTbl, '') + '%''
      -- AND ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'',''SSISDB'')
           '

-- Remove the '--' from the last statement in the WHERE clause to exclude system tables
INSERT INTO @AllTables (
	DBName
	,SchemaName
	,TableName
	)
EXEC sp_MSforeachdb @SQL

SET NOCOUNT OFF

SELECT 'Backup' AS [Action]
	,('bcp.exe "select * from [' + DBName + '].[' + SchemaName + '].[' + TableName + ']" queryout "' + @@Servername + '_' + DBName + '_' + TableName + '.bcp" -N -S ' + @@Servername + ' -T -E') AS [Script]
FROM @AllTables
WHERE DBName NOT IN (
		'master'
		,'tempdb'
		,'msdb'
		,'ReportServer'
		,'ReportServerTempDB'
		)

SELECT 'Restore' AS [Action]
	,('bcp.exe [' + DBName + '].[' + SchemaName + '].[' + TableName + '] in "' + @@Servername + '_' + DBName + '_' + TableName + '.bcp" -N -S ' + @@Servername + ' -T -E') AS [Script]
FROM @AllTables
WHERE DBName NOT IN (
		'master'
		,'tempdb'
		,'msdb'
		,'ReportServer'
		,'ReportServerTempDB'
		)

Reference: http://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table