Create Single Table Backup

There are many ways to create backup for a single table in SQL Server database. 

In SQL Server 2008, you can create insert statements for selected tables. 

Export wizard is commonly used to create a flat file backup for a single table. 

Except above method I found following code by SQL Expert Fred.

USE AdventureWorks

GO

DECLARE @table VARCHAR(128),

@file VARCHAR(255),

@cmd VARCHAR(512)

-- If i need to create CSV file Product table then

SET @table = 'Production.Product'

SET @file = 'C:\BCP_OUTPUT\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112)

+ '.csv'

SET @cmd = 'bcp "AdventureWorks.' + @table + '" out "' + @file + '" -S. -T -c -t,'

EXEC master..xp_cmdshell @cmd

Above code uses BCP to create a CSV file for a given table. You can create a template of above code, and then just load, change values and execute. This method has a drawback. It creates a CSV file for all rows but it has no column header row. So how can I import this table later on, without column header row?

Then I found quickest and simplest way to achieve this goal. Simply type select * query but before execution

• Click on Tools > options and change values for Query Result Output Format and Custom Delimiter (I preferred pipe sign “|”)

• Press Ctrl+Shift+F buttons, so it can save result to file.

SELECT * FROM Production.Product

• On execution, provide file name and your desired path and it’s done