How do I transfer data between SQL Server databases / across servers?

A. There are a variety of methods :-

1. The fastest method for whole databases is to use the DUMP DATABASE and LOAD DATABASE commands. You need to make sure that the databases are the same size and made up of the same segment fragments in the same order. If you do an sp_help_revdatabase on both this will allow you to check the required DDL for this. You can DUMP and LOAD from a local tape device and transport the tape if you do not have a network connection. (With SQL 7.0 the commands are BACKUP DATABASE and RESTORE DATABASE)

2. If you only want tables/data you can use the SQL BCP.EXE tool. This is a command-line program and is fully doc'd in the books-online. It works on one table at a time and allows you to create a flat file on disk.

3. For stored-procedures/views etc. there is an old command-line based tool called DEFNCOPY.EXE that works like BCP. It isn't used much these days unless you still have SQL Server on OS/2 - though it still works on NT at least up until 6.5.

4. SQL Enterprise Manager comes with a built-in gui transfer function, and SQL 7 comes with a separate, equivalent tool based on DTS. This allows transfer of all objects between two databases/servers but requires a network connection between the two.

5. The transfer tool supplied with SQL EM is exposed via the DMO interface and can be called using the SQLOLE calls from TSQL or your own VB program for automation purposes. See Q152801 for an example of how to do this.

6. 3rd-party DBMS management tools no doubt offer similar/better transfer/scripting tools to the above.

===

v1.03 2000.02.02
Applies to SQL Server versions : All
Related FAQ articles : n/a
Related Microsoft Kb articles : n/a
Other related information : n/a