Take Specific SQL Table backup in Sql server using query

--===============================================================

-- How to Take Specific Table backup in Sql server using query

--===============================================================

 

-- SQL Table Backup

DECLARE @table VARCHAR(128),

@file VARCHAR(255),

@cmd VARCHAR(512)

SET @table = 'DbName.UserName.TableName' --  Table Name which you want to backup

SET @file = '"D:\DbBackups\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112) --  Replace D:\DbBackups\ to destination dir where you want to place table data backup

+ '.dat"'

SET @cmd = 'bcp ' + @table + ' out ' + @file + ' -n -U UserName -P Password -S ServerName'

 

--print @cmd

EXEC master..xp_cmdshell @cmd

 

 

Note:

 

    You must have bulk import / export privileges

    In above Script,

-n denotes native SQL data types, which is key during restore

-T denotes that you are connecting to SQL Server using Windows Authentication, in case you want to connect using SQL Server Authentication use -U<username> -P<passord>

    This will also tell, you speed to data transfer, in my case this was 203568.15 rows per sec.

    Once this commands completes, this will create a file named "DbName.UserName.TableName_20131116" is a specified destination folder

 

--============================================

--Restore Table from backup

--============================================

--If Table not exist then create restore table from orginal table using below query

--Select * into DbName.UserName.TableName_Restore  from DbName.UserName.TableName where 1=2

 

BULK INSERT DbName.UserName.TableName_Restore

    FROM 'D:\DbBackups\DbName.UserName.TableName_20131116.dat'

    WITH (DATAFILETYPE='native');