The situation: You have a SQL server with a server collation that is incorrect for your usage and you need to change the entire server collation as opposed to a single database. This usually occurs because you have programs that are conducting server wide operations but are incompatible with the collation in use.
I ran into this problem earlier and it took far longer to sort out than it should have. This was because the articles missed something I’m very fond of: usable example code.
The Solution: Relatively simple, follow the steps below to change the collation.
- Take Backups – This process will disconnect any attached databases and while they can usually simply be reconnected I would strongly suggest making sure you have backups of all user databases (not system databases, by restoring a system database you undo the collation change!)
- Script out or backup schemas and users – All user and schema information in the master database will be lost so if you have anything custom in this regard that you can’t easily recreate make sure you get a script to recreate it once you’ve recreated the database.
- Run this command in the SQL install files directory on your install media
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=domainsfoley /SAPWD=P@ssw0rd! /SQLCOLLATION=Latin1_General_CI_AS
Things to note:
MSSQLSERVERis the default instance name, if you are using a non default instance you will need to change this
domainsfoleyis the windows account that will be added as admin
P@ssw0rd!is the password that will be created for the SQL SA account – note this MUST be a strong password or the operation will fail
Latin1_General_CI_ASis the new collation
And once the command has run successfully (please note the error messages pop up only briefly when running in QUIET mode so you may miss them and not realise the operation has errored):
- Check the collation has changed (Right Click on the server is SSMS and select Properties to check)
- Attach or restore your databases and recreate any lost users
A final tip, if you run into problems remove the
/QUIET argument and click “yes” to prompts until you see the error in a readable format so you can resolve it.
Hope this helps somebody else in need.