Thursday, March 29, 2012

Copy objects with DTS Import/Export Wizard?

Has the functionality of copying objects using the DTS Import/Export Wizard been removed in SQL Server 2005?

MSDN says this about the wizard:

"With the DTS Import/Export Wizard, you can transfer database objects such as indexes, views, roles, stored procedures, and referential integrity constraints. For more information, see Copy SQL Server Objects Task."

But the "for more imformation" link describes adding a task to a DTS package to copy an object, not how to use the wizard to copy objects.

It's been awhile since I've used SQL Server 2000, but as I recall it was possible to copy objects as well as data using the DTS Import/Export Wizard.

Thanks,

Ron

DTS has been enhanced to SSIS(Integration Services) in SQL 2005, so in this case you can use SSIS package to perform that copy SQL Server objects tasks which is similar to the copying the database objects.|||

You have a few options:

Copy Database Wizard:
Use this if you want to copy an entire database.

Right-click on a database in Management Studio, go to "Tasks", and choose "Copy Database...".|||

I also share in their frustrations... When you only have a few objects, in this case tables to transfer; you have very little options in the SQL2005 Import/Export wizard as compared to the SQL2000 DTS (Import/Export) wizard. Copy Database is out of the question for this scenario.

My problem lies in the fact that the SQL2005 Import/Export wizard does not create the indexes and keys when the table needs to be created in the target database, even if the source schema exists on the target database. Is there any way around this without pre-creating the target tables from scripts generated on the source DB? Seems that this manual step should be built into the wizard? Ay suggestions? We are running SP2 for SQL 2005.

There are suggestions that SSIS can be used, thats fine, but given the entirely different interface as compared to DTS, I don't have the time to deal with that when the wizard should be able to do this in a few minutes.

|||That option is fine for SQL2000/2005 to SQL2005, but is there any option to copy to SQL2000?|||I am getting very frustrated, for two days now I've been trying to work out how to copy a database (SQL express version) to my ISP which has the full MS 2005 server. I keep losing keys, stored procedures etc. When you right click on the database as you say and click on tasks there's no copy database there.|||Yey! now I get to spend 2 hours with ssis doing what used to take 2 minutes with dts import wizard.|||

I now use MS Database Publishing wizard which attaches to Database Explorer in Microsoft Developer Express. It works better than DTS Import/Export as I do not lose keys and can copy all object in the database. Great for making backups too.

No comments:

Post a Comment