Export or save a db table to local db

Sometimes it can be useful to dump a table from dev, stage or prod SQL databases to local machine, for backup, troubleshooting or some experiments. This is a how to guide using the BCP (Bulk Copy Utility) tool.

Ensure you have the BCP tool installed.

From a terminal run: bcp and ensure it displays the BCP options.

Save data

To save a full table to local csv file you can run bcp "dbo.TABLENAME" out OUTFILE -S tcp:SERVER -d DATABASE -G -U USER -t"," -c -q
- Replace TABLENAME with name of table, OUTFILE with path to file, SERVER with server, DATABASE with the database, and USER with your Entra username.
- Example: bcp "dbo.UserState" out UserState_20240729.csv -S tcp:prod-bank-use2-sqlserver.database.windows.net -d prod-user-use2-sqlDb -G -U victor.blomqvist@majority.com -t"," -c -q

Load data

To load a dumped csv, first ensure you have a matching table in your local DB. Then run bcp "dbo.TABLENAME" in INFILE -S tcp:localhost -d DATABASE -U sa -P PASSWORD -t"," -c -q
- Replace TABLENAME with name of table, INFILE with path to file to import, DATABASE with the database, and PASSWORD with the local DB password.
- Example: bcp "dbo.User" in User_20240729.csv -S tcp:localhost -d Test -U sa -P XXXXXXX -t"," -c -q