Changing SQL collation for deployed instance without reinstall (almost)

IMPORTANT UPDATE: If you use K2 4.6.11 or newer you have to have SQL_Latin1_General_CP1_CI_AS collation on a SQL Server instance which hosts K2 DB (more details)

Collation matters. Period. If you not sure just wait till you get an incident or issue which will teach you that it is. Some apps require you to have specific collation for their back end DB. For example for K2 you need to have  Latin1_General_CI_AS collation (see my earlier post on that _ ).

Nonetheless it can often be the case that your RTFM/Prerequisites_Check moment (I wonder if I can copyright/trademark this term? I heard that Michael Buffer trademarked his “let’s get ready to rumble” phrase _ ) happens after SQL server instance has been already provisioned. For newly created (clean install) K2 database you just need to delete database which was created on instance with wrong collation and allow K2 Setup Manager to create it again after you deal with your instance collation. There is an MSDN article detailing that _ but I will outline the procedure below.

Aforementioned MSDN article says that you have to have installation media to perform this operation, but in fact you can get away with Setup Bootstrap folder which should present on your server in location similar to one below (may vary from version to version, see details _ )

C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014

So steps are the following:

0. In case you have any non system databases attached to this instance take a note of database(s) file location, frequently it is just default SQL instance data folder common to all databases. Save this path or paths to notepad. 

1. Locate setup bootstrap folder for your SQL server installation, open CMD or PS window (don’t forget “dot net sourcing” thing for the latter):

# SQL 2012
cd "C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012"
# SQL 2014
cd "C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014"
# SQL 2016:
cd "C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\SQLServer2016"
# SQL 2017:
cd "C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\SQL2017"
# SQL 2019:
cd "C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\SQL2019"

2. Execute the following command:

setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=Domain\User /SAPWD=P@ssw0rd /SQLCOLLATION=Latin1_General_CI_AS

Once this operation completes  master, model, msdb, and tempdb system databases are rebuilt (dropped and re-created) with collation you specified in the command. Obviously all your modifications to these databases are lost (if you made any) and essentially it is sort of reinstall/repair type of operation just much quicker and without need to wade through SQL Setup GUI.

3. In case there were non system databases attached to this SQL instance (see step 0) they all are detached now. See my other blog post explaining how to attach all the databases from specific folder.