Changing Default Collation

How can i change the default collation for my Production database Server.

Thanks

mostymAsked:
Who is Participating?
 
jdlambert1Connect With a Mentor Commented:
Of course it's always wise to do backups before system changes like this, even on a test server, and you should backup your master & msdb databases, in addition to your user databases. I hate to say there's little risk, because even if no one else ever had a problem with it before, it doesn't guarantee that you won't. Worst case, your database becomes unrecoverable (never heard of that happening from a change to collation), so you restore from backup. In that unlikely event, you could create a separate database, set it up to match your production system, then import the data from production.
0
 
jdlambert1Commented:
There you go again, posting in the generic Database category and not mentioning which database brand/version you're using... :)

Judging from the question and your prior questions, I'm guessing you need this for SQL Server. If so, here's an example from SQL Server Books Online:

ALTER DATABASE MyDatabase COLLATE French_CI_AS
0
 
xabimondConnect With a Mentor Commented:
In order to change the default collation for the server you need to rebuild the master database using the rebuildm utility.

You'll need to backup yours dbs before and recreate and restore or re-attach afterwards as the master holds the details of the other dbs on the server.

The databases default collations can be changed using ALTER DATABASE as above.
0
 
mostymAuthor Commented:
Thanks Guys,
Yeah my bad. It is SQL Server 2000.
I need to change the defaukt collation for my test database server to make it the same as my production server.
What r the risks involved if any..?

thanks a ton though... :-)

mostym
0
 
xabimondCommented:
If you need to change the collation for the server then you need to use rebuildm - have a look in BOL for how to run it.  There is no point backing up the masterdb as this will be recreated, but backup the others.

Once you've rebuilt the master - specifying the correct collation :-) - you can restore your other databases.  However, these will then be a different collation to the tempdb which can cause a collation conflict if you use temp tables in your app and join between your database and join temp table on a character colunmn.  You can change the default collation on a database, but this wont affect any existing columns.  To get around this recreate the db using a script - EM can generate one for you - without specifying the collation.  Then dts the data between old collation database and new default collation database.  If you don't use temp tables then you don't have to worry about this last bit!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.