SQL Server installed with wrong collation and the process to change it

Yes just another installation. Click Next Next.. boom! Great.

Oops somebody just realised that there was some information missing initially about the collation. Of Course SQL DBAs don’t make mistakes so it must be somebody else right?

Besides also giving a thought to having a formal “Pre and post installation checklist” if one doesn’t exists or perhaps update it if it does exists.Next blog post perhaps..

Now… back to the problem at hand. How do we deal with this.

Well basically you can’t in the traditional way of making a small change from GUI or a quick script. I know not great news but all is not lost really. The earlier you find out about an installation having wrong collation the better before it goes live or out of your hand.

The way the workaround works is that you rebuild system databases with new settings. Since you are going to technically re-install (rebuild system databases) your configuration will be lost. So backup everything you want to restore or make note of.Again “Pre and post installation checklist”would be invaluable which would include questions to insist on from the source of request.

  1. Backup all databases, both System and users.
  2. Backup and / note all config
  3. Detach databases as after System rebuild you will only see an installation with fresh system databases.
  4. Job and security etc. (Hopefully you haven’t gone so far building or using with this server but if you did then yes)

From the command line at path of Installation media the following piece of commands needs to changed in the right places to start of.

SQL_Latin1_General_CP1_CI_AS is used just as an example of course, it should be replaced with whatever the correct one is.


Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=. /SQLSYSADMINACCOUNTS=domain\user /SAPWD=Longsecurepassw04d! /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

Hope this helps

Advertisements
About

IT professional with wider interest in technology

Tagged with: , , ,
Posted in sql, SQL Server, tsql

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: