Changing SQL Server Collation - IT CAN BE DONE!
A problem I come across regularly enough for me to write an article about it, is SQL server Collation and setting it up with the wrong one. Historically I would simple burn the install down using this handy command and then reinstall it again, which is easy peasy when you use DSC ( See my other post)
Setup.exe /Q /Action=Uninstall /FEATURES=SQLEngine,AS,IS /INSTANCENAME=Instance
Now this is all well and good when you can operate scorched earth but what happens if you cannot burn it down. Well you can force a collation change using the below line of code
CD C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\SQLServer2016
Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS='AD SYSADMIN ACCOUNT' /SQLCOLLATION=Latin1_General_CI_AI
Be aware though this has a few drawbacks you should consider
- It will flatten and rebuild all the SystemDB's, Yep, As you can imagine logins and Credentials etc will all go!
- Your code may change how it works if it suddenly starts running on a different collation server, bare that in mind.
- Make sure you have access to the server after you change the sysadmin account, the line /SQLSYSADMINACCOUNTS will add in only those accounts specified, if your AD group isnt one of the you aint getting back into the Server ( Done that before)
- Who knows what other configurations are gone.
- If you are using DSC be aware that it may try and set the configuration back or report a change in State every 15 minutes after you've run this.
- Databases are left intact
- Be aware of orphaned accounts
In short its a pretty useful bit of knowledge for rectifying your post setup muck up but I am always in favor of the Scorched Earth approach, if your script is good enough you should be able to tweak the collation and re run it.