.::: Backup & restore table SQL Server Using bcp & SQL CMD like MariaDB (Export SQL Server to csv /txt using t-sql ):::.

 

A. Source (backup data )

1. check table barang 
  

2. Backup data as table using bcp (export mssql to csv txt)

bcp teguhth.dbo.barang out "C:\Backup\barang.txt" -c -t, -S localhost -U usertest -P passwordsql

bcp barang out "C:\Backup\barang.txt" -c -t, -S localhost -U usertest -P passwordsql -d teguhth
  

3. export / record structure table using export script or using save scrip. after check script 
 

B. Destination (Restore data)

1. create table as same as source address or using import script 

sqlcmd -S MSSQL -U usertest -P passwordsql -d bcpdb -i C:\Backup\barang_sqlschema.sql

2. import data using bcp 

bcp bcpdb.dbo.barang in "C:\Backup\barang.txt" -c -t, -S localhost -U usertest -P passwordsql
bcp barang in "C:\Backup\barang.txt" -c -t, -S localhost -U usertest -P passwordsql -d bcpdb
 

3. check result table 
select * from bcpdb.dbo.barang ; 

 

c. Export to csv / txt 

 

1. export to txt / csv using tsql 

DECLARE @FileName VARCHAR(255);
DECLARE @BCPCommand VARCHAR(8000);
DECLARE @Today VARCHAR(10);
DECLARE @Query VARCHAR(2000);

-- A. Identify Today's Date
SET @Today = CONVERT(VARCHAR(10), GETDATE(), 120);

-- B. Set the File Name (Example: pembelian_2024-05-20.txt)
SET @FileName = 'C:\bcp\pembelian_' + @Today + '.txt';

-- C. The Instructions (Select data from Machine 2)
SET @Query = 'select * from teguhth.dbo.pembelian';

-- D. The Export Command (-t\t means use 'Tab' to separate columns)
SET @BCPCommand = 'bcp "' + @Query + '" queryout "' + @FileName + '" -c -t\t -T -S ' + @@SERVERNAME;

-- E. Execute the command
EXEC xp_cmdshell @BCPCommand;
 

2. export to txt / csv using tsql <remove space>

DECLARE @FileName VARCHAR(255);
DECLARE @BCPCommand VARCHAR(8000);
DECLARE @Query VARCHAR(2000);
DECLARE @Today VARCHAR(10);

SET @Today = CONVERT(VARCHAR(10), GETDATE(), 120);

SET @FileName = 'C:\bcp\pembelian_' + @Today + '.txt';

SET @Query = 'SELECT * FROM teguhth.dbo.pembelian';

SET @BCPCommand = 'bcp "' + @Query + '" queryout "' + @FileName + '" -c -t -T -S ' + @@SERVERNAME;

EXEC xp_cmdshell @BCPCommand;
 

3. export to csv simple with comma 

DECLARE @FileName VARCHAR(255);
DECLARE @BCPCommand VARCHAR(8000);
DECLARE @Query VARCHAR(2000);

SET @FileName = 'C:\bcp\pembelian.txt';

SET @Query = 'SELECT * FROM teguhth.dbo.pembelian';

SET @BCPCommand = 'bcp "' + @Query + '" queryout "' + @FileName + '" -c -t, -T -S ' + @@SERVERNAME;

EXEC xp_cmdshell @BCPCommand;