Export (0) Print
Expand All

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.

ms190923.note(en-US,SQL.90).gifImportant:
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.

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.

ms190923.note(en-US,SQL.90).gifNote:
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...).

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:

ms190923.note(en-US,SQL.90).gifNote:
For information on copying complete databases, see Copying Databases to Other Servers.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft