ColdFusion Muse

DTS Export Part 2

Mark Kruger May 19, 2005 2:05 PM MS SQL Server Comments (1)

Well obviously I need to catch up on DTS. Dave Ross pointed out to me that there is an option for "copy objects and data" that is a part of the DTS wizard. I never tried that option. I simply didn't absorb what it said. In my head I had the 3 options separated as

  • Copy Tables
  • Copy using a query
  • Copy the schema only (objects - tables, users etc.)
As you can see what it actually says is copy objects and data between servers.

Having tried it I can tell you it does a perfectly wonderful job of copying the data, views, stored procedures etc. There is even an option to eliminate the object level permissions (like owner). And it doesn't generate constraint errors because it's smart enough to copy the objects and data in an appropriate order. Thanks Dave - you just saved me some time - even if I have to eat crow (ha).

  • Share:


  • dave ross's Gravatar
    Posted By
    dave ross | 5/19/05 3:51 PM
    don't worry... it took plenty of late nights for me to figure out the best way to move things around w/ DTS. For a year I did it exactly how you said before. The biggest problem was that the copy objects and data option wouldn't copy over my column defaults. Turns out that column defaults aren't real MSSQL "defaults", which is a type of object i never use, they are actually just extended table properties. Once I started checking the "extended properties" box on the options window, my life got a whole lot easier. Not letting it copy users/permissions is great too, as you mentioned.