Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Changing Default Collation

Posted on 2004-10-01
5
Medium Priority
?
600 Views
Last Modified: 2012-06-22
How can i change the default collation for my Production database Server.

Thanks

0
Comment
Question by:mostym
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12204535
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
 
LVL 1

Assisted Solution

by:xabimond
xabimond earned 100 total points
ID: 12215810
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
 

Author Comment

by:mostym
ID: 12218902
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
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 100 total points
ID: 12219008
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
 
LVL 1

Expert Comment

by:xabimond
ID: 12219955
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question