Changing the SQL Server Collation After Installation for SQL Server 2012 or 2014 without re-installation


So I was asked recently to setup a test environment for one of our CRM applications. So I went ahead and instead of copying the existing Virtual Machine I decided to create a new one from scratch. Installed SQL Server 2012 and the required application files. As I went to install the database onto the SQL server I had realised that I selected the wrong server database collation during installation (I chose SQL_Latin1_General_CI_AI). I’ve known in the past with 2008 / 2008 R2 I needed to uninstall and reinstall to change the Server Collation. This is no longer the case with SQL Server 2012 or 2014.

After going over the documentation of the software, I needed a Server Collation of SQL_Latin1_General_CP1_CI_AI. I’ll make a couple of recommendations with this method, if you already have databases mounted, back them up, then remove them (as this speeds up the process a fair bit if you have DBs with heaps of rows and indexes). Not sure how supported this is by Microsoft, I’m doing this in an environment where even though it is live data, it isn’t critical.

Anyway, moving on. Firstly, shutdown any SQL Server services that are running and then fire up an Administrative command prompt window and go to the SQL Server Binn directory. Once there, we will issue the following command (you can replace the collation with which ever you need.

sqlservr -m -T4022 -T3659 -s"MSSQLSERVER" -q "SQL_Latin1_General_CP1_CI_AI"

The command above, launches a single user admin mode to connect to the SQL Server -m, enables Trace mode using -t. We specify the instance with -S and set the collation with -q. This will run through the motions and then notify you that the operation has completed and that no user action is required (so close off the window). Start up any SQL services we killed off earlier and the databases will now have the correct Collation (restore if required).


One response to “Changing the SQL Server Collation After Installation for SQL Server 2012 or 2014 without re-installation”

Leave a Reply

More Posts

RDP to Windows Login Screen

I was recently resurrecting an old demo environment in AWS which consisted of a few EC2 virtual machines, however upon trying to login, I quickly realised that the account password had expired and by default Windows Remote Desktop doesn’t have an ability to change passwords since you’re not presented with the logon screen.  We didn’t […]

Microsoft 365 Admin Notifications to your user mailbox with Plus addressing

Licensing your Entra Administrator accounts for email is a common practice to ensure you are across service health updates, user, security and billing alerts but by doing this it introduces security risks and additional costs for mailbox licensing. As a best practice, privileged accounts should remain isolated from unnecessary communication channels to minimise vulnerabilities or […]