Freigeben über

FAQ (With a little history), How do I copy tables with Keys and constraints from one Server/DB to another Server/db?

  • Artikel

I see this question come up in 2 main contexts, one is express users the other is users who thought they knew how to do this in SQL Server 2000 and are now confused in SQL Server 2005.

We'll start with SQL Server 2000...

In general the starting point for this problem is the DTS Wizard (more often referred to as the import/export wizard in 2000 onwards). In SQL 7/2000 the wizard is sneaky while offering a lot of functionality. Consider the following screen in the wizard (its the 3rd screen), it offers 3 seemingly simple options to the unaware...

Let’s take a second to remember how DTS actually works. DTS is essentially a big OLE DB Consumer/Producer app. It uses pretty much every option in the OLE DB APIs to look at a source system through those APIs and then using OLE DB produce something on the destination in terms of schema and data. Now we tried to not special case specific databases and to only use OLE DB based runtime knowledge but there was too big a diff in the capabilities in the different OLE DB Providers for that to be possible. So while in general the code is pretty clean, there are a couple of VERY hairy areas of code where there is knowledge of specific Providers and also databases embedded in there. Extra points for being able to identify the following providers using their code names, which are referenced in the code;

Luxor, JOLT, Canoe, Odin, Kagera

Hence DTS is more of an OLE DB tool than a SQL Server tool and with the exception of one or 2 settings like support for Fastload there is no special insight of SQL Server.

Having just said all that is not strictly true in the broadest sense, many folks think of DTS as a data import export tool, actually that part of it, called the DataPump internally which is materialized as the Transform Data Task and the Data Driven Query task, is the OLE DB based engine. But DTS also has an extensible job flow system(the package) that has a limited relationship to the OLE DB based pump that I referenced early. However tasks can still deal with data but they can use any technology they want. Other tasks that deal with data are the ExecSQL Task, Bulk Insert and Copy (Transfer) Objects tasks.

Ok so back to the wizard, if you choose option 1 or 2 then the wizard generates a series of ExecSQL tasks and also Transform Data Tasks, one per table, that first create the table on the destination and then copy the data point to point from the source to the destination using OLE DB. This pulls over the table/column DDL only, no constraints, keys or anything like that. While much of that it is defined in OLE DB and we had it working in Beta 1 of SQL Server 2000, it was pulled for release because stabilizing it, given the different providers, was proving to be a bear. So the key take away from this option is, lots of execsql and transform data tasks, with basic table schema and data. The screenshot below is the output from generation that makes a copy of northwind into tempdb.

So what happens if you choose option 3?

Well first of all you get some very different options shown here;

The reason for the differences is that by choosing 3 vs 1/2 you have now completely changed how you are using DTS. This option generates a Copy Objects Task, which in turn calls DMO Transfer, which is an ODBC based mechanism. Copy Objects does just that, it copies ALL objects from one SQL Server(and only SQL Server) to another SQL Server(again only SQL Server) using DMO's Scripting and BCP. It does all this via a single task as it obviously does not use the DataPump.

Now this subtle change from 1/2 to 3 confused the hell out of many people, not just the capabilities but also the implementation, that subtle selection has a huge impact. Hence when we were working on the new Wizard for SQL 2005(it was a rewrite) we decided to remove the 3rd option as it was adding to code bloat/complexity and was confusing to customers given it was really trying to solve a different problem (sql to sql vs ole db to ole db) than the other options.

Ok so we pulled it from the confusing but easy to find place, but where did we put it? Its in the copy database wizard in SQL 2005. This sort of makes sense given that CDW is about copying a database and its dependencies from one place to another; however the original vision for CDW was much grander. If you think about what the CDW and Copy Objects do, they are just different levels of granularity of solving the same problem. That problem is deployment/copying of a database, its dependencies and its contained objects with the ability to filter at any level from tables down to rows of data in the DB. The original plan was to ask the user a couple of questions about how much control they wanted over the objects and what speed (doing detach/attach is MUCH faster than script out/in) and then for the wizard to decide on the implementation method (detach/attach or Copy Objects) based on the answers to the questions. However this proved a lot harder to get right that we expected so in the end a new option was added to CDW that takes you down the old path of Copy Objects, you can see it here.

Now I think its fair to say that we really should have come up with a better name for the option(I'll take the flak for not catching that one) as it really does not help anyone to find it or understand what its actually for.

The other thing to note here is that while DMO Transfer used Scripting, BCP and ODBC under the covers, the new transfer actually uses Integration Services as the engine, this leads you to the weird situation in the screenshot below, where you have an SSIS Package that has a task that generates an SSIS package at execution time...

All right and so to answer the question (finally I hear you say) if you want to copy SQL Server schema and data from one DB to another then use CDW, if you want fine grained control choose the second option in the wizard.

Comments

  • Anonymous
    July 24, 2006
    I'll agree that the new location makes more sense, but requiring sysadmin rights just to copy objects from one database to another is highly inconvenient (in a shared hosting environment, for example).
  • Anonymous
    August 01, 2006
    The comment has been removed
  • Anonymous
    August 02, 2006
    Good information - would like to see the answer to the second question also - import for Express users.
  • Anonymous
    August 08, 2006
    The comment has been removed
  • Anonymous
    August 11, 2006
    Thanx, wold be better if you mentioned, the other way i.e. 2005 one also.

    To use the Copy Database Wizard, you must be a member of the sysadmin fixed server role on the source server, as well as on the destination server. In other words, you must be an administrator on the server from which the database is to be copied, as well as an administrator on the server to which the database is being copied. To transfer the databases using the detach and attach method, you must have file system access to a file system share containing the source database files.



    Copy Database Wizard SQL Server Integration Services Custom Tasks
    The Copy Database Wizard is built as a SQL Server 2005 Integration Services (SSIS) package that runs on the destination server. After the wizard screens have been completed, SQL Server 2005 automatically names and saves the package on the destination server. The package is saved whether or not it is run immediately, scheduled for a later date, or scheduled on a recurring basis.

    To start the Copy Database Wizard
    In SQL Server Management Studio, in Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Copy Database.
  • Anonymous
    August 11, 2006
    To use the Copy Database Wizard, you must be a member of the 'sysadmin' fixed server role on the source server, as well as on the destination server. In other words, you must be an administrator on the server from which the database is to be copied, as well as an administrator on the server to which the database is being copied. To transfer the databases using the detach and attach method, you must have file system access to a file system share containing the source database files.



    Copy Database Wizard SQL Server Integration Services Custom Tasks
    The Copy Database Wizard is built as a SQL Server 2005 Integration Services (SSIS) package that runs on the destination server. After the wizard screens have been completed, SQL Server 2005 automatically names and saves the package on the destination server. The package is saved whether or not it is run immediately, scheduled for a later date, or scheduled on a recurring basis.

    To start the Copy Database Wizard
    In SQL Server Management Studio, in Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Copy Database.
  • Anonymous
    September 13, 2006
    The comment has been removed
  • Anonymous
    November 17, 2006
    The comment has been removed