Solved

Changing Default Collation

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

Thanks

0
Comment
Question by:mostym
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 25 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 25 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: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

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

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

623 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