Changing Server Collation on SQL Server 2008

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Blesson John » Changing Server Collation on SQL Server 2008

19 posts, Page 1 of 2

1 2 »»

Changing Server Collation on SQL Server 2008

Rate Topic

Display Mode

Topic Options

Author

Message

blessonblessons

Posted Saturday, September 18, 2010 1:38 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 7:08 AM
Points: 55, Visits: 394

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

Post #988773

afterx

Posted Monday, September 20, 2010 9:51 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, March 18, 2012 7:54 PM
Points: 3, Visits: 171

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

Post #989516

Paul Smith-221741

Posted Monday, September 20, 2010 11:38 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, June 10, 2013 6:55 AM
Points: 427, Visits: 278

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

Post #989619

blessonblessons

Posted Monday, September 20, 2010 11:54 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 7:08 AM
Points: 55, Visits: 394

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 BigGrin

Post #989631

SQLRNNR

Posted Monday, September 20, 2010 4:26 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 12:32 AM
Points: 18,855, Visits: 12,439

thanks for the article.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #989830

Paul Smith-221741

Posted Tuesday, September 21, 2010 5:56 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, June 10, 2013 6:55 AM
Points: 427, Visits: 278

If you need such a script, then I've got it BigGrin

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

Post #990159

DBA Cabuloso

Posted Monday, September 19, 2011 2:02 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 7:21 AM
Points: 193, Visits: 211

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

Post #1177455

philip.kraft

Posted Friday, March 09, 2012 2:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 14, 2013 10:29 AM
Points: 5, Visits: 54

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!

Post #1264195

blessonblessons

Posted Friday, March 09, 2012 5:48 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 7:08 AM
Points: 55, Visits: 394

Hi Phil,

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

Thanks
Blesson.

Post #1264297

philip.kraft

Posted Friday, March 09, 2012 5:56 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 14, 2013 10:29 AM
Points: 5, Visits: 54

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.

Post #1264301

« Prev Topic | Next Topic »

19 posts, Page 1 of 2

1 2 »»

Permissions