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).
Leave a Reply