How to change Collation on server level


We made mistake when we installed SQL Server 2008 with wrong collation.

I know it's n't recommended to change the Collation on server level,But What is the opinion of the experts on this site it's recommended to change the collation on the server level ? and what is the best way to do that?

more ▼

asked Dec 09, 2012 at 06:09 PM in Default

Troy_2012 gravatar image

750 26 37 39

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

As recommend by Microsoft, http://msdn.microsoft.com/en-us/library/ms179254(v=sql.105).aspx, I think that I would either

a) Reinstall with the correct Collation


b) Change the collation of the new databases.

Given that option 'B' I think would get confusing and most likely a database would be missed and cause havoc later in life, I would most likely opt for a complete reinstall. Do you already have user databases on the server and is it in production?

Just my opinion though.

more ▼

answered Dec 10, 2012 at 03:04 AM

JohnM gravatar image

6.9k 1 3 7

Yes this is the problem we have already users db and the server in production .
Dec 10, 2012 at 03:32 AM Troy_2012
How many databases are we talking about?
Dec 10, 2012 at 03:38 AM JohnM
just one database and it's mirror
Dec 10, 2012 at 04:51 PM Troy_2012
In that case, I'd opt to scripting out the mirroring configuration , schedule an outage, backup the database, and nuke & pave the instance. Once the instance has been reinstalled & reconfigured, you can restore the database and re-configure mirroring via the script. Just a thought.
Dec 10, 2012 at 05:00 PM JohnM
(comments are locked)
10|1200 characters needed characters left
Seriously, go for the re-install option. Collation issues are a major ball-ache for an incoming DBA, so much so that I contributed a test in @Brent Ozar's sp_Blitz - http://www.brentozar.com/blitz/ - http://www.brentozar.com/blitz/database-server-collation-mismatch/
more ▼

answered Dec 10, 2012 at 02:25 PM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

(comments are locked)
10|1200 characters needed characters left

Reinstalling with correct collation is the only solution check the below link http://sqlbuzz.wordpress.com/2011/08/20/how-to-rebuild-master-database-aka-rebuilding-sql-server-2008r2/

more ▼

answered Dec 10, 2012 at 05:50 AM

ramesh 1 gravatar image

ramesh 1
2.2k 63 67 69

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 09, 2012 at 06:09 PM

Seen: 1217 times

Last Updated: Dec 10, 2012 at 05:00 PM