We are doing a migration/consolidation at my current client from a standalone based SQL 2000 environment (20 servers, 50 databases) to a clustered SQL 2000 environment (we are not ready for 2005 yet). We have linked servers all over the place, and for the most part many linked servers will go away with this migration/consolidation.
Yes, I have about 300-500 sprocs, views, UDFs, DTS jobs to update (remove/change linked server entries), but my problem lies with the final data migration in which I may not have a large window to do so, a weekend at best.
My plan was to store all updated sprocs, views, UDFs, and other user objects and then simply do a final restore and then drop and recreate all the new objects following the database restore/cutover that I will have stored in various .sql files. I thought about simply migrating the data via scripts (disable constraints, triggers, enable identity_insert, etc.) and to just migrate the data instead of ,loading final db and recompiling updated objects with new server link adjustments.
Any ideas on how to make this as simple as possible, am I wrong with my "restore and recompile" migration plan? I have always done it this way in the past, but certainly not at the scale of this project (about 200GB of data).