Manage Learn to apply best practices and optimize your operations.

Change collation to SQL Server 2005 after upgrading databases

Want to change the collation to SQL Server 2005 standards after upgrading databases from SQL Server 2000? Learn the best approach involving DTS and SSIS.

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

Dig Deeper on SQL Server Stored Procedures

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.