Changing Microsoft SQL Server Collation

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.

  1. 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!)
  2. 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.
  3. 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:

    • MSSQLSERVER is the default instance name, if you are using a non default instance you will need to change this
    • domainsfoley is 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_AS is 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):

  1. Check the collation has changed (Right Click on the server is SSMS and select Properties to check)
  2. 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s