Copy objects with DTS Import/Export Wizard?

Ron31416

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

Satya SKJ


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.

Paul Mestemaker - MSFT

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...".

Using the Copy Database Wizard:
http://msdn2.microsoft.com/en-us/library/ms188664.aspx

Import and Export Wizard:
Use this if you only want to copy a subset of objects (just tables/views) and data.

Right-click on a database in Management Studio, go to "Tasks", and choose "Import Data..." or "Export Data...".

Generate Script Wizard:
Use this if you want to create a script that will create objects (tables/views/sprocs).

Right-click on a database in Management Studio, go to "Tasks", and choose "Generate Scripts..."

Service Pack 2:
We've made many enhancements to the Copy Database Wizard and Generate Script Wizard in SQL Server 2005 Service Pack 2. You can get the SP2 December CTP here: http://www.microsoft.com/sql/ctp.mspx.

SP2 will be coming out of beta and officially released within the next 6 weeks. For more information, please visit my blog: http://blogs.msdn.com/sqlrem/archive/2007/01/12/SP2-and-BPA-FAQ.aspx.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

SteveTroiano

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.

therippa

That option is fine for SQL2000/2005 to SQL2005, but is there any option to copy to SQL2000

Minessence

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.

John Dieter

Yey! now I get to spend 2 hours with ssis doing what used to take 2 minutes with dts import wizard.

Minessence

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.