Tuesday, 31 January 2012

Table backup in SQL server Using BCP utlity

 Hi All,

When a Developer asking you to run a Query and you found that it deleting a 100 table and as a DBA you need to have backup of the entire 100 Table since the developer may come back again.

One of the best way to do this is by BCP Utility .


EXPORT :
***********


C:\Documents and Settings>bcp "select * from <DB Name>.<Schema name>.<Table Name>" queryout "test.bcp" -N -S <Server name> -T -E

Example :
C:\Documents and Settings>bcp "select * from master.dbo.finance" queryout "test.bcp" -N -S STLDEMO1 -T -E

Starting copy...

2 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 15     Average : (133.33 rows per sec.)

Starting copy...

2 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 15     Average : (133.33 rows per sec.)


Exported file will be in C:\Documents and Settings .check for BCP file .


IMPORT :
************
To Import this to the destination database

C:\Documents and Settings> bcp <DB Name>.<Schema name>.<Table Name> in "test.bcp" -N -S <Server name> -T -E

Example :

C:\Documents and Settings> bcp master.dbo.finance in "test.bcp" -N -S STLDEMO1 -T -E