SQL Server Performance Forum – Threads Archive

SQL Server Collation Set Change

Can I change a SQL Server Collation Set fgor an entire server? If so, what do I need to do? I can’t find any documentation that says I can change the collation set at server level. Thanks.

---

check this :
http://msdn2.microsoft.com/en-us/library/ms179254.aspx

---

Changing the collation is one of complicated one becuase you have to rebuild the system databases..
Make sure you have complete valid backup before starting this process… Here is one more article from MS…. Check the topic "Specifying a New System Collation"
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
Mohammed U.

---

You can set collation for database level and upto column level too, modifying your queries with required collation is enough as what Mohammed referring is right. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

That article only presents one way of doing – there is another. You do still need to run rebuildm.exe to rebuild your system databases. But you can detach your user databases before and re-attach them after. You don’t actually need to export all the data and reimport it. you can run alter table.. alter column statements to change the collation of each char/varchar etc column. But it is involved – you need to drop all dependent objects first (indexes, keys, defaults, etc.) But it may be less work than the article describes, depends on the complexity of your database. You do also need to change each user database’s default collation first too, using ALTER DATABASE dbname COLLATE newcollation Good Luck!

---

I have to reset the collation set of the server so it is the same as the databases. (I don’t know why this wasn’t set previously!) If I detach databases, backup system databases, rebuild master. How do I change collation set? Is it ok then to restore msdb and master databases or do I need to change something now they are in a different collation set? Does anything need to change, or are there more complications) if this is on a virtual server (on a cluster)? Thanks.

---

I don’t think you restore the master and msdb on a different collation set.
YOu can script logins with passwords and script the jobs, operators and alerts from MSDB. Mohammed U.

---

http://www.dbazine.com/sql/sql-articles/larsen3
http://www.databasejournal.com/features/mssql/article.php/1587631 Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

So your user databases already have the correct collation – that makes it easier. Detach the databases, then run rebuildm.exe. During the rebuild process you’ll be asked for the collation you want (for system databases). You can reattach your databases afterwards. As explained by Mohammed U you cnanot restore master and msdb after this – you need manually to recreate all logins, linked servers, jobs, etc. You can script the jobs and use DTS for the logins (although you’ll need to store them temporarily on an intermediate server while you’re running rebuildm).

---

]]>