Post-install change of Instance Collation in MS SQL 2008 r2 / 2012

Sometimes people don’t know what they want, until you start bothering them about it.

In this strictly hypothetical case, you relied on your default collation settings picked by the SQL server installer since no other hints had been given by the client.

This choice of collation was of course wrong!

Now you need to change the instance collation, which pretty much wipes all your instance settings and you have to reconfigure much of it (if not all) since it will rebuild your system databases.

You’ll need the installer available. Simply issue the following command:

setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=MSSQLSERVER /SAPWD=”r@ndomp4ss0wrd” /SQLSYSADMINACCOUNTS=BUILTIN\ADMINISTRATORS mydomain\myuser /SqlCollation=Latin1_General_CI_AI

This will also reset your DB/LOG locations on SQL Server 2008, didn’t happen for my SQL 2012 instance though.

I encountered the following error message with SQL 2012 when running above command:
“The Windows account “Art\Vandelayblabla” does not exist and cannot be provisioned as a SQL Server system administrator.”

Thats because I had encapsulated the sysadminaccounts with citation marks (“), which apparently sucks. So even if you supply multiple accounts you still can’t use “ for encapsulation, really annoying if you are used to *nix standards/common sense.
For multiple accounts just separate them with whitespace (as in my example).

Reading up

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 )

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