Changing Server Collation on SQL Server 2008

  • blessonblessons

    SSC Eights!

    Points: 929

    Comments posted to this topic are about the item Changing Server Collation on SQL Server 2008

  • afterx

    SSC Enthusiast

    Points: 161

    There is also an undocumented way of doing this which is all automated. This will change the server and all databases on the instance. Of course this should be tested before running it on your production system, and make sure EVERYTHING is backed up...

    1.) Find when your instance is running and use that path to find where sqlservr.exe is located.

    2.) Check to ensure you have plenty of disk space.

    3.) After the backups are done open a command prompt and navigate to where sqlservr.exe is located. If you are using Server 2003 just open an command prompt. If you are using Server 2008 be sure to open an admin command prompt.

    4.) Then run the command below.

    5.) Once you hit enter you will see the following

    Command:

    Replace latin1_general_ci_ai with whatever you want to change the instance too.

    sqlservr -m -T4022 -T3659 -q"latin1_general_ci_ai"

    or

    sqlservr -m -T4022 -T3659 -q"latin1_general_ci_ai" -sINSTANCENAME

    This has worked for me several times and is much quicker and easier than other methods.

    -JM

  • Paul Smith-221741

    SSCommitted

    Points: 1923

    Rather than Changing the Collation on the server to Match the Database, Why not change the Collation on the database to match the Server.

    As you correctly spell out, it leaves the collation on the individual columns in their original state, but it is a straightforward process to set up scripts to change them

  • blessonblessons

    SSC Eights!

    Points: 929

    One reason to change the server collation was because the database is replicated from another server and the collation for that server is different from the destination server.

    Anyway changing database collation is not that straight forward.In my case I scripted out all the tables,procedure etc and replaced the collation with new one.Ran the script and then bulk inserted the data.

    Or else a script to drop(disabling foreign keys also doesn't allow to change collation) all the foreign key and triggers etc,change collation of character fields and then finally place all the constraints back.(It would be great if anyone who has created such a script to share it).I managed to complete the first half (dropping/disabling foreign keys/triggers etc).But creating the foreign key became a tedious task as I had to keep track of quiet a few field and the last thing I wanted was to find that some of the foreign keys and triggers are missing after the go live happens 😀

  • SQLRNNR

    SSC Guru

    Points: 281210

    thanks for the article.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Paul Smith-221741

    SSCommitted

    Points: 1923

    If you need such a script, then I've got it:-D

    It may need a bit of work but it does the majority of things

    It runs as a total of 10 scripts.

    the 1st 4 generate scripts of the things to put back afterwards

    5th Removes All the bits that stop the collation changes (FK, PK, Contraints etc)

    6th Changes the Collation on All (n)char, (n)varchar, (n)text columns

    7 - 10 are the scripts generated from 1-4

  • DBA Cabuloso

    SSCommitted

    Points: 1733

    The undocumented process told by Alexf works until the 2005 version, in the 2008 it does not work. This is because the Server Collation in 2008 can't change unless it is restarted.

    Dba Cabuloso

    Lucas Benevides

    ________________
    DBA Cabuloso
    Lucas Benevides

  • philip.kraft

    Old Hand

    Points: 327

    Good morning JM.

    I came across you article after trying to find a way to change or server collation for SQL Server 2008 R2 std.

    Am I right the command you posted is "sqlservr -m -T4022-T3659 -q "COLLATION" -s "INSTANCENAME"?

    This seems to only kick of the setup and I was wondering if you have any further experience in resolving Server Collation issues?

    Any help would be massively appreciated!

    Regards.

    Phil - the struggling DBA!

  • blessonblessons

    SSC Eights!

    Points: 929

    Hi Phil,

    Is this a production server and are there any user databases on that server?

    Thanks

    Blesson.

  • philip.kraft

    Old Hand

    Points: 327

    Currently this is our OpsManager server. All the user DB's have been backed up and detached. So its a bare instance. SQL Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (VM). I am trying the same on my local installation af tsql (Enterprise) But still no luck!?

    Thanks for the quick reply!

    Phil.

  • blessonblessons

    SSC Eights!

    Points: 929

    Will help you change the collation.It is good that you have backed up the database and scripted out all the user defined jobs associated with the instance.Also script out the user logins.

    If you have customized any other setting at the server level then make note of them.Run sp_configure to get all the server level setting.

    The command to change the server level collation is to:-

    Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName

    /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]

    /SQLCOLLATION=CollationName

    This will basically rebuild the system databases with the new desired collation.This method is faster than reinstalling SQL Server.

    refer the below link

    http://msdn.microsoft.com/en-us/library/ms179254.aspx

  • blessonblessons

    SSC Eights!

    Points: 929

    In case you are wondering where the setup file is you will find it on the install CD or depending on whether it is a x86 (32-bit) install or x64 (64-bit) install the file location will vary.

    eg:- for x64 (C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2)

  • philip.kraft

    Old Hand

    Points: 327

    Perfect! Thanks. I actually just managed to make a success of it on my local machine with the script you gave me! Server Collation was changed from Latin1_General_CI_AS to Latin1_General_CI_AS_KS_WS with no issues!

    I will try it on the sandbox server and let you know.

    Thanks so much.

    Phil.

  • philip.kraft

    Old Hand

    Points: 327

    No luck on the server like I had on my local instance :crying:

    Y:\>setup /Q /IACCEPTSQLSERVERLICENSETERMS /ACTION=REBUILDDATABASE /INSTANCENAME

    =MSSQLSERVER /SQLSYSADMINACCOUNTS=MyDomain\MyUsername /SAPWD=thesapassword /SQLCOL

    LATION=SQL_General_CP1_CI_AS

    Microsoft (R) SQL Server 2008 R2 Setup 10.50.1600.01

    Copyright (c) Microsoft Corporation. All rights reserved.

    The following error occurred:

    The state of your SQL Server installation was not changed after the setup execut

    ion. Please review the summary.txt logs for further details.

    Error result: -2068643838

    Result facility code: 1203

    Result error code: 2

  • blessonblessons

    SSC Eights!

    Points: 929

    Can you please attach the log file for review?

    Thanks

    Blesson

Viewing 15 posts - 1 through 15 (of 20 total)

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