September 15, 2015 at 3:00 am
Hi All,
Is it possible to change the collation of your instance?
As far as I know it is not possible to change the collation once SQL Server has been installed.
When I said that SQL needs to be reinstalled at work the guys sent me this link:
I ran it exactly like that but the collation didn't change.
I have also restarted the services but the collation is still the same.
Am I running this code wrong or am I right about not being able to change the collation after installation?
September 15, 2015 at 3:27 am
The page you linked describes how to change the server collation. It should work and if it doesn't check the setup logs for errors.
However I'm under the impression that you want to change the collation of all user databases, which simply won't happen by running setup.
There is a way to do that in a single pass, but it's undocumented and unsupported. It's described here: http://spaghettidba.com/2011/05/26/changing-server-collation/
-- Gianluca Sartori
September 15, 2015 at 3:33 am
The only thing which that process does is change the default collation for the instance (collation new databases will get by default), the collations of master and TempDB. It doesn't affect the user databases.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 16, 2015 at 12:39 am
Hi Guys,
Thanks for the feedback.
I did it on a test server and just want to share my experience.
This should never be done on a production server. When you change the collation it rebuilds the system databases if you follow the instructions on that link. Since it rebuilds msdb as well it removes all the jobs from the instance. You also have to configure DB mail again and re attach all the user databases.
Thanks guys
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply