DTS failure when transferring database objects to new database.

[uncleroydee]

When I attempt to create a copy of an existing database on the same server I encounter a problem.

I create the new database with no user objects, then use DTS to copy the user defined database objects (but not copy any data) from an existing database to the new one. The process seems to work most of the way through, but then stops with the error "Failed to copy objects from Microsoft SQL Server to Microsoft SQL Server". When I double click on the step, I see the explanation "Insert Error: Column name or number of supplied values does not match table definition."

The database contains a significant number of triggers and stored procedures and it is possible that table definitions have changed since some of the SPs and triggers were created although I have not experienced any failures of the triggers or SPs. Would that cause the error that I am getting?

Thanks.

 

[jhall156]

I hate to thrash around and try to figure which object is the culprit by just inspecting them. It's easier in my opinion to just move them one at a time til I find the rotten one. This can be complicated if you have lots of dependencies (this view needs that view etc.). JHall

 

[uncleroydee]

Thanks.

It's true that it's difficult to try and move objects one by one, when almost every has a defined relationship to one of the other tables in the database.

I wanted to have different logical filenames for the data and log files, but I may just blow it off and back up the first database and restore it as the second database using the "With Move" parameter in the restore statement.