Copy Database Wizard fails on creation of view into another not-yet-copied database

Question

Update - I found that doing a manual detach/reattach using MSDN article "How to: Move a Database Using Detach and Attach (Transact-SQL)" got around this issue. I'll just be creating a script to dettach and reattach but do the file copies manually. Any info on how to overcome the problems with the wizard would be helpful in the future.

I am in the process of moving around 20 databases from our current server to a new one. When performing the copies however I have found that some databases can not copy if they have views into other databases that have not yet been copied to the target system.

The log file generated says "failed with the following error: "Invalid object name" in reference to the database in the view. If I first copy just the database referenced in the view and then in a separate step copy the database over containing the view it is successful. However some other database have views into each other so can't just adjust the order in which the copy occurs.

Is there any way to ignore this error and just allow everything to copy?

Answer 1

I had a similar problem when trying to use the copy database wizard in sql server 2008. When it would try to copy a view that depended on another view I would get the "Invalid object name" error. To fix these errors I used the sp_refreshview stored procedure to refresh the dependency information for each of my views. I had to execute it on the views in the same order as the dependencies were created. For example, if I had three views a, b, and c and c depended on b which then depended on a then I would have needed to execute:

EXECUTE sp_refreshview 'a';
EXECUTE sp_refreshview 'b';
EXECUTE sp_refreshview 'c';