Copying Data Between Servers

To bulk-transfer data from one Microsoft SQL Server database to another, data from the source database must first be bulk-exported into a file. The file is then bulk-imported into the destination database.

Important

For a database that uses the simple recovery model, after bulk-importing data into a table, performing a differential backup is recommended. For a database that uses the bulk-logged or full recovery model, a log backup is sufficient. For more information, see Creating Full and Differential Backups of a SQL Server Database or Working with Transaction Log Backups.

Copying Data Between Servers Using bcp or Transact-SQL

You can use the bcp command to export or import data and the BULK INSERT or INSERT ... SELECT * FROM OPENROWSET(BULK...) Transact-SQL statement to import data.

The bcp commands can be used to copy data in native, character, or Unicode format between different instances of SQL Server on different processor architectures. A format file can be used, if flexibility is required. The format or format file that is used to export the data must also be used to import the data.

Note

If you are copying data between server instances that use different collations, see Copying Data Between Different Collations.

Storing Information in Unicode Native Format

Storing information in Unicode native format is useful when information must be copied from one instance of SQL Server to another. If you use native format for noncharacter data, you will save time and prevent unnecessary conversion of data types to and from character format. Character loss is possible if extended characters are copied into non-Unicode columns and the extended character cannot be represented. You can prevent the loss of any extended characters by using Unicode character format for all character data when bulk-transferring data between servers that use different code pages. However, a data file in Unicode native format can be read by bcp or the Transact-SQL statements that support bulk import—BULK INSERT or INSERT ... SELECT * FROM OPENROWSET(BULK...).

Additional Methods for Copying Data Between Databases

In addition to using bcp or Transact-SQL, generally, you can copy data from one SQL Server database to another using any of the following:

Note

For information on copying complete databases, see Copying Databases to Other Servers.

See Also

Concepts

Importing Native and Character Format Data from Earlier Versions of SQL Server
Optimizing Bulk Import Performance
Scenarios for Bulk Importing and Exporting Data
Using Unicode Character Format to Import or Export Data

Other Resources

BACKUP (Transact-SQL)
bcp Utility
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
RESTORE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance