After you create the table objects on the destination database, you can safely use bcp out to bulk copy out the tables one at a time from the source database to files. If you copy data to and from SQL Server, you can—and probably should—use a native-type copy because SQL Server can read and write the native-type format more efficiently. Then, all the files you just created can be bulk-copied back into the destination database, one at a time. After you complete the bulk copying, you can execute on the destination database the remaining commands from the SQL scripts that you generated earlier. Executing the remaining commands after bulk copying is completed creates all the key constraints, triggers, and indexes that belong on the destination tables but weren't created earlier.
Why Not Use the DTS Import/Export Wizard?
Although transferring data by using bcp isn't complicated, you might wonder whether an easier method exists. In SQL Server 2000 and 7.0, DTS is one alternative for transferring data. DTS has many positive features. It's graphical and easy to use. A DTS shortcut in the Start menu opens the Import/Export Wizard. And the utility features handy wizards, integration with Enterprise Manager, and advanced capabilities for performing complex data transformations that aren't available in bcp. You can also use DTS to transfer data to and from heterogeneous data sources.
If DTS is so feature-laden, why should you ever consider using bcp to copy data? The best reason to use bcp rather than the DTS Import/Export Wizard is speed of data transfer. The Import/Export Wizard is great for transferring a small amount of data or for performing data transformation. Because the wizard often takes less time to set up than a bcp command, the wizard has a special advantage with smaller datasets. However, the wizard's time value diminishes as datasets become larger and bcp begins to outperform the wizard. Also, the wizard can't handle nonlogged data transfers, so the rate of transfer decreases in direct proportion to the amount of time SQL Server takes to write to the transaction log.
However, an equally important reason not to use DTS is that DTS doesn't always create a destination table that exactly duplicates the source table; so by default, you might lose constraints, indexes, and identity columns in the transformation. This problem isn't crucial if you transfer only small amounts of data on a day-to-day basis. However, when you move or copy a database, you have to ensure that all database objects transfer as they were originally defined. By using bcp and the SQL scripts that Enterprise Manager generates, you create a destination table just like the source table, along with its constraints, indexes, and identity columns. Because the data that moves between the two databases is identical, the two tables are functionally equivalent.
How About Database Restore?
SQL Server 7.0 introduced another alternative to using bcp to move data: the database restore feature. To transfer data by this method, you simply create a database backup file, then restore the database backup file into a new database. The database restore feature is completely integrated into Enterprise Manager and incorporates wizards that make backing up and restoring a database simple for even the newest DBA. The destination database is, in fact, an exact copy of the source database, and the transfer is fast.
So why not use database restore to copy data? The best reason not to use the database restore feature to copy data is loss of control. When you restore a database from a backup file, the new database is exactly the same as the original. In other words, all the mistakes that you made in the source database end up in the new database. If the transaction log is hundreds of megabytes too large in the source database, it will still be several hundred megabytes too large in the destination database. All the indexes that you haven't rebuilt lately won't be rebuilt in the destination database. Also, using the database restore feature doesn't give you the option of copying only selected objects; a database restore copies all or nothing. Using bcp and the SQL scripts that Enterprise Manager generates takes only the space that the objects themselves require and just enough space on the transaction logs for building the constraints and indexes. Executing the SQL scripts on the destination database also delivers a bonus: The procedure rebuilds all the indexes. By using bcp, you can pick and choose the objects that belong in the destination database.
Giving Bcp Its Due
SQL Server ships with several tools for copying data from one database to another. Since Microsoft introduced new graphical data-transfer tools in SQL Server 7.0, bcp has probably become the least often discussed utility and the hardest to learn. However, for copying large sets of data as quickly as possible, bcp is still one of the best tools for the job. Any DBA with an occasional need for moving large amounts of data should become acquainted with the basics of using bcp.
End of Article
Prev. page
1
2
[3]
next page -->
