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

Fixing the Outlook Web Access web application failed to initialize error

During the Christmas break many still wish to check their e-mails whilst enjoying their holidays.  One of my Clients who don’t use OWA are now taking the plunge and going to be checking their e-mail (great feature). With SBS 2008 (so Exchange 2007), OWA is enabled by default, so I thought there would be nothing […]

Upgrading your current KMS Server (Server 2008 R2) to support Windows 8 and Server 2012 activation

If you’re in an enterprise environment, chances are that you have a KMS server running (usually either Windows 7 or Server 2008 R2).  So now that Windows 8 and Server 2012 has hit VLSC for SA customers you’ll most likely want to begin testing and performing pilot deployments but we need to get our Key […]

Hello World

So here is my new blog, one which I actually intend on working on. Feel free to browse around, comment and critique. You can also browse the pages and contact me via the pages link or follow me on twitter. Hope you enjoy my blog and the articles that I write.