Change sql server collation

  • Hi Guys ..
    I would like to change sql server collation . Can I just reinstall sql and change the collation during the installation ?

    Or is there anything that i need to pay attention ? 

    Many thankss

    Cheers...

  • Yes, you can do that.  Make sure you back up all your user database first, and script out important system stuff such as jobs and logins.  Bear in mind that when you've put your users databases back on to the server with the new collation, those databases will have the same collation as before and therefore you may get collation conflict errors if you use temp tables in your code.

    John

  • The is an undocumented engineering script you can use to change the collation on an entire server, which includes all databases, all fields and all indexes. It's also very quick. I have used this a few times myself. It works as long as the collation you are changing too is different to the current system databases. So for example if you had just restored a database to the server that was Latin1_General_CI_AS but the server was SQL_Latin1_General_CP1_CI_AS and you wanted to change the database to match the server you would have to run this twice. Once to change the server to Latin1_General_CI_AS and then again to change everything to  SQL_Latin1_General_CP1_CI_AS. This is a godsend, it saves hours and hours of work.

    1.       From a command prompt run: (making sure the instance name is correct)
    NET STOP "SQL Server (MSSQLSERVER)"
    2.       Confirm Y at the command prompt that you want to stop the services.
    3.       From the command prompt run: (making sure the instance name is correct, and changing the collation name to suit).
    sqlservr -m -T4022 -T3659 -s"MSSQLSERVER" -q"SQL_Latin1_General_CP1_CI_AS"
    4.       At the end of the sequence you should receive the messages: “The default collation was successfully changed and Recovery is complete” and “This is an informational message only. No user action is required”.
    5.       At the command prompt type Ctrl + C to shutdown SQL Server and then type Y to confirm you want to shutdown SQL Server
    6.       Now restart the SQL Server and SQL Server Agent Services

  • WhiteLotus - Tuesday, February 28, 2017 6:12 PM

    Hi Guys ..
    I would like to change sql server collation . Can I just reinstall sql and change the collation during the installation ?

    Or is there anything that i need to pay attention ? 

    Many thankss

    Cheers...

    The supported route is to run the installer and rebuild the system databases, you will need to restore any user databases or objects like jobs, logins, etc.

    General steps are;

    • If the instance is a clustered instance, offline the cluster group using the following PowerShell cmd
      stop-clustergroup -name thegroupname

    • Launch setup.exe either from the install DVD or from the local cache folder. Valid locations are amongst the following
      C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012
      C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release

    • Once you have set the correct path in the DOS command prompt, use the following command line. If instance is mixed mode, sa pwd is required
      setup.exe /quiet /action=rebuilddatabase /instancename=theinstancename /sqlsysadminaccounts="domain\a user or group" /sqlcollation=SQL_Latin1_General_CP1_CS_AS /SAPWD=strong password

    • Once the installation has completed, online the cluster group using the following PowerShell cmd
      start-clustergroup -name thegroupname

    • Check the collation using the following T-SQL cmd
      SELECT SERVERPROPERTY('Collation')

    • Reinstall your objects

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • This was removed by the editor as SPAM

  • JasonClark - Wednesday, March 1, 2017 10:33 PM

    WhiteLotus - Tuesday, February 28, 2017 6:12 PM

    Hi Guys ..
    I would like to change sql server collation . Can I just reinstall sql and change the collation during the installation ?

    Or is there anything that i need to pay attention ? 

    Many thankss

    Cheers...

    There is no need to reinstall. You may also change this on installed SQL Server. Have a look: https://www.mssqltips.com/sqlservertip/2901/how-to-change-server-level-collation-for-a-sql-server-instance/ 
    Hope, this will help you..................

    same as what i posted then

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply