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


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 (8)

  1. 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).

  2. T. Baumgarten says:

    The CDW is a great tool as long as you’re in a homogeneous SQL Server 2005 environment, but if you’re in a large shop running a mixture of SQL Server 2000 and 2005 servers, AND you need to be able to copy data objects back and forth between servers running each type of release, then it comes up short.  

    Although the CDW can transfer *FROM* SQL Server 2000 databases, it is apparently hard-coded to prohibit any transfers *TO* SQL Server 2000 databases.  On the other hand, any attempt by a SQL Server 2000 DTS package to connect to a SQL Server 2005 database is summarily rejected.  (I certainly could have missed something, but I couldn’t get it to work, though not for lack of trying.)

    Maybe Microsoft will implement a means of greater interoperability between 2000 and 2005 in the next 2005 service pack (SP2), but until then, DBAs appear to be stuck with manual scripting and/or other workarounds to automate database object transfer between 2000 and 2005.

  3. Luke Petersen says:

    Good information – would like to see the answer to the second question also – import for Express users.

  4. Euan Garden says:

    Kristopher – Yes we know this is a problem and are looking at it for the future.

    T. – CDW disallows transfer from 2005 to 2000 for a couple of reasons. First is that because it uses IS and it follows a pull model, this means that IS must be on the destination server (ie the 2000 server). Second reason is that CDW copies objects directly, the problem here is that many 2005 objects and their propertiesa re not supported on a 2000 server, so this is disallowed.

    DTS 2000 should be able to connect to a 2005 server, make sure you use SQL Native Client (SQLNCLI) not OLE DB though.

  5. Unpredictable says:

    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.

  6. Unpredictable says:

    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.

  7. Philip Kuok says:

    My issue with the new CDW is that even after I chose to copy objects using SMO, there is little I can do from within CDW to specify the objects I want to copy. The purpose with wizard is so that it can simply task, not separate them into packages that I will have to edit further.

    All I wanted to do was to copy the extended properties over; this was in plain sight as a check box "include extended properties" in SQL 2000 Enterprise Manager; couldn’t this be added to CDW?

  8. Matias Nino says:

    Euan wrote: "if you’re in a large shop running a mixture of SQL Server 2000 and 2005 servers, AND you need to be able to copy data objects back and forth between servers running each type of release, then it comes up short."

    "It comes up short" is the key phrase. I deal with a large shop myself, only it’s shared hosting for SQL servers and there are about 200 other databases on the same server that my database is on, and when the DCW gets to the point where you have to select a database to copy, it errors out if there are other existing databases that my SQL login doesn’t have access to. Hence, leaving me with no easy way to copy my tables WITH the darn KEYS that my ASP.NET app needs in order to manage its datasets properly.

    Thank god for your blog, because I haven’t found any place on the net at all that can show me an EASY FREE way to copy my commercially provided shared hosting SQL 2000 Database WITH KEYS to my own network with EM 2005.

    Now it could be that my commercial database provider is screwed up with their multi-user db management configuration, but you would think Microsoft would’ve anticipated, as they usually are able to do, the (I’m sure they have a better term for this) ‘commercial dependency’ and made sure their software could handle such an instance.

    So, I just noticed SP2 is out. And what would you know, it does mention something about CPW with improved reliability with ‘dependences’. I truly hope this fixes my problem. If not, GET TO WORK ON SP3! (hah! Just kidding)

Skip to main content