This is a quick guide on using the bcp.exe program to export and import data.
A while ago i needed to downgrade a customers 100GB database located in a MSSQL2008R2 server to MSSQL2005. And since downgrading is not supported i needed to do a export and import.
bcp is a command line tool that are installed together with Microsoft SQL Server that allowes you to export and import data.
The first time i did the export and import I run a problem when doing the import. I did not recognize the table definition of the bcp file. But this is solved by creating a format file and use that format file when doing the export and import.
bcp.exe support both trusted windows authentication and SQL account.
To use window authentication you specify the -T parameter and then it will use the logged in user account for connecting to the database. If you want to use SQL account you use the -U and -P parameter.
Use the -S parameter to specify what SQL Server Instance and server you want to connection to. Eg -S (local)\MSSQLSERVER
Connect using Windows Authentication
bcp.exe <command> -S (local)\MSSQLSERVER -T
Connect using SQL User
bcp.exe <command> -S (local)\MSSQLSERVER -U sqlaccount -P sqlpassword
Create Format file
The format file is a file explaning how the table is defined. the bcp tool can generate this file for you.
bcp.exe table format nul -f formatfile.fmt -S (local)\MSSQLSERVER -U sqlaccount -P sqlpassword
You will not have a formatfile.fmt and whenever you now export and import data for that table you need to include that format.
Exporting data can be done in a couple of ways. By specifying the 'out' parameter you will export and entire table. If you want to export a result of a query and can specify the parameter 'queryout'. If you export using query you must be sure that the format file you use are matching the returned result.
Export and entire table
bcp.exe mydatabase.dbo.ValueTable out exportdata.bcp -f formatfile.fmt -S (local)\MSSQLSERVER -U sqlaccount -P sqlpassword
Exporting using query
bcp.exe "SELECT * from mydatabase.dbo.ValueTable WHERE valTime >= '2010-01-01'" queryout exportdata.bcp -f formatfile.fmt -S (local)\MSSQLSERVER -U sqlaccount -P sqlpassword
To import data from a export file from bcp you use the 'in' parameter. And to make sure it reads the file correctly we also need to specify the format file to use with '-f'. with the -b parameter you can specify how large batches to use. Easy batch is imported and logged as a separate transaction that imports the whole batch before committed.
bcp myDatabase.dbo.ValueTable in "D:\ExportedData\exportdata.bcp" -b 1000000 -f formatfile.fmt -S (local)\MSSQLSERVER2 -U sqlaccount -P sqlpassword