Changing Server Collation on SQL Server 2008

Question

Changing Server Collation on SQL Server 2008

blessonblessons

SSC Eights!

Points: 959
More actions
September 18, 2010 at 1:38 pm

#228017

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

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

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

afterx SSC Enthusiast Points: 161 More actions · Answer 1

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 SSCrazy Points: 2003 More actions · Answer 2

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: 959 More actions · Answer 3

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: 281334 More actions · Answer 4

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 SSCrazy Points: 2003 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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: 959 More actions · Answer 8

Hi Phil,

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

Thanks

Blesson.

philip.kraft Old Hand Points: 327 More actions · Answer 9

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: 959 More actions · Answer 10

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: 959 More actions · Answer 11

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 More actions · Answer 12

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 More actions · Answer 13

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

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: 959 More actions · Answer 14

Can you please attach the log file for review?

Thanks

Blesson