To bulk copy data from one Microsoft® SQL Server ™ database to another, data from one SQL Server installation must be bulk copied to a data file, which is then bulk copied to the other SQL Server installation.
After bulk copying data into a table, it is a good idea to back up the database. Then, as necessary, re-create any indexes on the table, reset any database options, check to make sure that any triggers that need to run are executed, and make sure the select into/bulkcopy option is set to false.
Note Native, character, and Unicode format bcp can be used to bulk copy data between different computers running SQL Server on different processor architectures. However, the same format must be used when importing as exporting.
Storing information in Unicode native format is useful when information is to be copied from one computer running SQL Server to another. Using native format for noncharacter data saves time, preventing unnecessary conversion of data types to and from character format. Using Unicode character format for all character data prevents loss of any extended characters when bulk loading data between servers using different code pages (character loss is possible if extended characters are copied into non-Unicode columns if the extended character cannot be represented). However, a data file in Unicode native format cannot be read by any program other than bcp or the BULK INSERT statement.
It is also possible to copy data from one SQL Server database to another using:
| BACKUP | Distributed Queries |
| INSERT | RESTORE |
| SELECT | Data Transformation Services Import and Export Wizards |
| Unicode Character Format bcp | Optimizing Bulk Copy Performance |