none
How to change SQL server 2008 collate after installation

    Question

  • Hi,
     
    On windows sever 2008 I've installed SQL server 2008 RC0.

    At the time of installation, I'd specified the collation as LATIN1_GENERAL_BIN, and now I want to change this collation to LATIN1_GENERAL_CI_AS.

    Please note that I don't want to change the collation of specific db but need to change the sql server 2008 server's settings.

    How to accomplish this requirement with minimum efforts?

    Thanks
    Tuesday, July 29, 2008 8:51 PM

Answers

All replies

  • I don't think it's possible - collation settings at setup determine system database collations, which can't be changed after installation (at least in 2005, and it doesn't seem to have changed in 2008). You'll need to reinstall the instance (or add another instance) with the desired server collation.
    Wednesday, July 30, 2008 12:03 AM
  • Yes you can but it requires rebuilding master and reloading your databases. You'd have to do this from the command prompt with something like

     

    Code Snippet
    start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword> SQLCOLLATION=<NewSystemCollation>

     

     

     

    This is for 2005. BOL documents this in detail under the How to: Install SQL Server 2005 from the Command Prompt or the section. Just go there and search for Specifying a New System Collation.

     

    2008 is very similar. See Setting and Changing the Server Collation or Setting and Changing the Database Collation in BOL depending on which you're trying to accomplish.

     

     

    joe.

    Wednesday, July 30, 2008 7:13 PM
  • Hello Joe,

     

    Thanks for your help.

     

    I tried with the command you given but still getting the problem.

     

    Details:

     

    start /wait Z:\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=MyPassword211 SQLCOLLATION=Latin1_General_CI_AS

     

    Here, Z:\ is a map drive which contains sql server 2008 setup files.

     

    It shows me following error:


    The Syntax of argument "/QN" is incorrect. Either the delimiter '=' is missing or there is one or more space characters befor the delimiter '='.

     

     

    Thanks

    Tuesday, August 05, 2008 12:56 PM
  • According to the link that Joe posted, the switch should be /QB, not /QN.
    Wednesday, August 06, 2008 7:53 PM
  • I also tried with /QB, but with this switch it's showing me same problem.

     

     

    Wednesday, August 06, 2008 8:06 PM
  • I hope /QB parameter no more exits in SQL 2008 for silent installation, you need to use /Q parameter for the same.

     

    Thursday, August 07, 2008 10:56 AM
    Moderator
  • I tried /Q option too, but it was also not working.

    Saturday, August 09, 2008 12:21 PM
  • Have you managed to get this to work with the RTM version? AFAIK, changing collation settings post install was not available until after RC0.

     

     

    joe.

    Thursday, August 28, 2008 4:08 PM
  • See the following blog with the correct syntax for rebuilding system databases in SQL Server 2008.  Also covers the syntax for changing the server collation.

     

    http://blogs.msdn.com/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

    Monday, September 08, 2008 9:20 PM
  • i uninstalled sqlexpress 2008 and re-installed it again to change the collation to Latin1_General_CS_AS, but how must now change my actual DATABASE's collation. i must rebuild it yes, but how do i do that??? i need to use setup.exe , i only have: SQLEXPR_x86_ENU. this is the application i used to install sqlexpress 2008...i got this application off microsoft site...

     

    thank you

    helloise

    Friday, September 19, 2008 9:55 AM
  • i rebuilt my db with a different collation. when i check the summary log it says collation is Latin1_General_CS_AS(correct), but when i open my db and check the field collation of a table it is still the old one?? how does that work i dont understand. can someone please xplain?

     

    thanks

    helloise

    Monday, September 29, 2008 9:07 AM
  • Yes, its possible. Either you've done one of two things.

    1) Took the backup of database which was having old collation and restore it on database with new collation.

    2) Detached the database on old collation and attached on database with new collation.

     

    In either of case, the table field collation will show old collation.

     

    I had same problem, I fixed it with the following ways.

     

    1) Create a empty database on new collation

    2) Restored the back up of old collation on new collation with diff database name

    3) Import table data from old to new one, making use of Import/Export data wizard.

     

    In this way what will happen is when you create an empty database, it will have new collation and when you import table data it will have new collation as well.

     

    Hope this helps

     

    C

    Wednesday, December 10, 2008 4:27 PM
  • Hi,

    you can change the collation by using the MS SQL Server 2008 Management Studio too.
    This would be the corresponding command, which runs -quiet- for a comparingly long time (4min) since the DOS Window is closed:

    E:\setup.exe /Quiet /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=x \Administrator /SAPWD=x /SQLCOLLATION=SQL_Latin1_General_CP850_BIN2

    After that you can find the log here:
    C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\Summary.txt


    Here is a screenshot:


    Axel Arnold Bangert - Herzogenrath 2009
    Tuesday, December 29, 2009 5:13 PM
  • i rebuilt my db with a different collation. when i check the summary log it says collation is Latin1_General_CS_AS(correct), but when i open my db and check the field collation of a table it is still the old one?? how does that work i dont understand. can someone please xplain?

    helloise

    SQL Server and database collations are DEFAULT collations. Actual collations in tables maybe quite different.

    A shocker: COLLATION is not table-level, rather column-level property. Therefore, text columns in a table must individually be rebuilt to the desired collation.

    Work around: just place COLLATE DATABASE_DEFAULT to the right side of an expression giving collation error.

    Related articles:

    http://www.sqlusa.com/bestpractices/changecollation/



    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012




    • Edited by Kalman Toth Wednesday, September 26, 2012 7:04 AM
    Saturday, January 09, 2010 7:11 PM
  • Hi,

    I have always used the below command. This has to be done after inserting the setup disk. Hope this works for you.

    Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
    /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]
    /SQLCOLLATION=CollationName

    Thanks
    Rohit
    Monday, February 01, 2010 10:21 AM
  • The above will work, but it's important to keep checking the log file:

    C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\Summary.txt

    As you may have to overcome any errors in there before it will let you apply the change. If you're trying to apply this change to a cluster, good luck, because i haven't been able to do it yet! ...Looking like a reinstall!
    Friday, February 19, 2010 3:23 PM
  • Hi Friends,

    It was a very healthy discussion. I would like to know the exact soultion to fix this issue.

    can anyone post the answer?

     

    Thanks,

    Ayyappan

    Wednesday, August 25, 2010 2:04 PM
  • It should be this one:

    Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName 
    /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ] 
    /SQLCOLLATION=CollationName
    
    

     

    However this is true only for microsoft SQL server 2008. The express version has a lot of bugs (varying from automaticly choosing collation according to language or regional option (depends on what OS), or a major bug in bootstrapped setup.exe or even on new installer which does not allow direct access to setup.exe).

    There are a several workarounds (But these do not help you to make your DB clean and neat):

    1. change the collation of the database, and not the collation of the server.

    2. install SQL server express 2005 WHICH ALLOWS YOU TO CHOOSE COLLATION IN THE INSTALLER! (jesus... what were they thinking in microsoft when they released MSSQLEXPRESS 2008?)

    • Proposed as answer by johanP325 Thursday, March 31, 2011 12:39 PM
    Tuesday, September 14, 2010 7:51 AM
  • hi, i had a similar challenge and not wanting to re-install the instnace, i looked around the management studio control and found the "FACETS" in the sql 2008 express. Do this by right-clicking on the db in question and select FACETS...You will find a editable field for Collation. There isn't a drop down list of all the types, so you wil have to know the name in advnaced.

    hope this helps...

     

    good luck,

    Thursday, September 16, 2010 1:03 AM
  • 1. change the collation of the database, and not the collation of the server.

    It should be noted that the references are to SQL Server default collation and database default collation. Actual column collations will not change by changing the defaults. You have to perform ALTER TABLE... ALTER COLUMN... to change column collation. Alternative is rebuilding (CREATE TABLE & populate) the table with the desired collation. See articles in my previous post.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012




    • Edited by Kalman Toth Wednesday, September 26, 2012 6:58 AM
    Friday, October 15, 2010 1:08 PM
  • will it work in cluster also ?? or we having some different way to implement in Cluster Env. 
    india
    Friday, July 22, 2011 7:10 AM
  • For cluster environemnt you just need to take the resource(SQL server service) offline from cluster admin and follow the same process. Check this one.

    http://www.sqlservercentral.com/articles/Collation/70685/

    Tuesday, August 02, 2011 10:15 PM
  • It works but it destroyed all my DB ... :/
    Tuesday, January 22, 2013 11:19 PM
  • It rebuilds all the system database, so you will loose any data stored in the system databases like logins, linked servers, jobs, etc. You need to attach your user databases so that you could use them.

    Tuesday, January 22, 2013 11:26 PM
  • It works but it destroyed all my DB ... :/

    Hello Julien,

    Can you explain more detailed, what happens, please?

    As V. Keerthi Deep wrote before, all attached databases + server logins are stored in the "master" database, so when you rebuild the system database, those informations get lost. You have to attach all before existing databases manually and you have also add all logins again.

    See TechNet Attach a Database and Create a Login


    Olaf Helper

    Blog Xing

    Wednesday, January 23, 2013 4:47 AM
    Moderator