IAN ESCARRO

Backup SQL Server Data using Bulk Copy Program

August 21, 2024

Sometimes Task->Generate Scripts takes too time to backup especially when you want to backup only selected tables in the database. This can be done easily with bcp, issuing the command in your power shell dumps the data in tab-delimited values into a file.

\> bcp "DATABASE.dbo.TABLE" out "C:\Downloads\TABLE.sql" -c -S "HOSTNAME" -U USERNAME -P PASSWORD

This is very useful for migrating data and for seeders where some tables are only needed to be seeded into the database as dependent values for other tables. We are heavily using this technique for testing. As for reference, here's our use case

Dump data into .sql files
Seed using QueryFromFile, this can be from another library that supports dumping SQL scripts. In our case, it's our own framework
Run automation; this can insert new values to other tables based from the seed data from .sql file
Rollback new values

We need to rollback each time since we want to have data the same as it was so we can run each time and expect the same results for our testing. If not, there should be error somewhere or a bug introduced in the new code that we need to fix.

That's it for now. See you in the next blog!

ABOUT IAN ESCARRO

Founded ICE Solutions.