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:
- The Integration Services (SSIS) Import and Export Wizard
This wizard can access a wide variety of data sources. You can copy data to and from SQL Server, flat files, Microsoft Access, Microsoft Excel, and other OLE DB providers. For more information, see Creating Packages Using the SQL Server Import and Export Wizard. - Distributed queries as part of an INSERT statement. For more information, see Distributed Queries and INSERT (Transact-SQL).
- The SELECT INTO statement, which specifies that the result set is used to create a new table. For more information, see SELECT (Transact-SQL).
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)