Quick Way to Create Single Table Backup


There are several ways to create backup for a single table in SQL Server database. In SQL Server 2008, now you can create insert statements for selected tables. Beside this Export wizard is commonly used to create a flat file backup for a single table. Both methods are reliable but still time consuming. And when I searched for quickest method to take backup of a single table 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 = ‘D: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

 

Code basically uses BCP to create a CSV file for a given table. I can create a template of above code, and then just load, change values and execute. So simple but still it has a drawback. It creates a CSV file for all rows but WITHOUT column header row. Now 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

Don’t worry about newly created file extension. When need to import just select it from All Files (*,*).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: