One of my clients is running a 7GB database in SQL Server 2000 and they're rolling everything out to SQL Server 2005. Basically, I need to copy the database from 2000 to 2005, but change the collation to the SQL Server 2005 standard.
So far I have been creating new databases (test), scripting the old databases and applying the scripts before importing the data via Data Transformation Services (DTS). This did not work, so I split the script up (tables, views, sp's, fk's etc). However, the scripts generated, call stored procedures, views or tables before they've been created and there always seems to be some views that get missed.
Can you suggest a smoother approach? I have read your article Step-by-Step Guide: Migrating to SQL Server 2005 with Copy Database Wizard, but it doesn't mention collation.Requires Free Membership to View
Depending on the collation change (binary to non-binary or vice versa), the best approach with native tools would be to script out the objects and then migrate the data via DTS or SQL Server Integration Services. Just be careful to make sure the collation is correct at the database, table and column levels when you are finished.
This was first published in December 2007
Join the conversationComment
Share
Comments
Results
Contribute to the conversation