How to change SQL Server Collation

• GaffneyJ

  SSC Eights!

  Points: 856

  More actions

  July 13, 2016 at 8:27 am

  #1889872

  Old thread I too have been having problems with this in the future...

  It Seems to run and not show any error messages...

  Command i am running: Setup /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=ecljagaf /sapwd=Password /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

  I am running this from this directory: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release

  I have also tried mounting the drive and running the command from there.

  any help would be appreciated.

• Solomon Rutzky

  SSCoach

  Points: 16430

  More actions

  June 27, 2018 at 3:24 pm

  #1995529

  What does the "sqlservr.exe -q" option do?

  Short answer:

  It changes the Collation at all levels: Instance, all Database's, and all string columns in all User Tables. It does this, and avoids all of the typical restrictions, by simply updating the meta-data of the tables, etc to have the new Collation. It then drops and recreates all indexes that have string columns. There are also a few nuances to this method that might have impact, but are fixable.

  Long answer:

  I have documented all of the behaviors, including listing all of the potentially affected areas by doing such a wide-sweeping Collation change, in the following post:
  
  Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

  Warning:

  This option is undocumented (well, not "officially" documented), meaning: unsupported (use at your own risk ; it might work perfectly, but if it doesn't, Microsoft isn't going to help or be liable)

  Take care, Solomon..
  

  SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
  Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
  Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
  Info sites — Collations     •     Module Signing     •     SQLCLR