Copying Data Between Servers
To bulk copy data from one Microsoft® SQL Server™ database to another, data from the source database must first be bulk copied into a file. The file is then bulk copied into the destination database.
After bulk copying data into a table, if the recovery model is simple, then a full or differential backup is recommended. If the recovery model is bulk-logged or full, a log backup is sufficient.
Note Native, character, and Unicode format bcp can be used to bulk copy data between different instances of 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 must be copied from one instance of 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 and 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:
- The DTS Import/Export Wizard.
- The Transact-SQL statements BACKUP and RESTORE (to copy entire databases).
- Distributed queries as part of an INSERT statement.
- The SELECT INTO statement.