Database collation Change

Question

Database collation Change

bspavan86

SSC Enthusiast

Points: 166
More actions
April 28, 2014 at 4:04 am

#307086

Hi my Production DB's Server level Collation is SQL_Latin1_General_CP1_CI_AS
where as my development DB's Collation is Latin1_General_CI_AI ,often our development
team asks us to create a copy of the Production DB on our development Environment this becomes a big task for us because when we create a new DB and restore it with production copy the it will have a collation(SQL_Latin1_General_CP1_CI_AS) which is different from our Development server's collation
Can anyone please tell an easier way of changing the Collation of newly restored DB(without changing the Server level collation of our development DB)

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

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

HanShi SSC-Dedicated Points: 33506 More actions · Answer 1

You can change the (default-)collation of a database with the ALTER DATABASE command. But this doesn't change the collation of the existing objects!! You need to change each existing object to the desired collation with an ALTER command. There's a script on codeplex to help you with this:

http://www.codeproject.com/Articles/302405/The-Easy-way-of-changing-Collation-of-all-Database

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

Usually collation should be same on Prod and dev. Backup of the database also contain the details of collation and while restore it will keep the same collation. The only option is ALTER DATABASE command to change the collation.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Lowell SSC Guru Points: 323504 More actions · Answer 3

changing the collation of the database is only a tiny 1% piece of the puzzle.

every varchar/char/nvarchar/nchar/text column in the database has it's own collation, that is not affected just because you change the database collation.

you have to change the individual columns as well, which can get very complex if defaults, check constraints, indexes or several other things are applied to any of those data type columns.

the default collation only affects newly created items that do not explicitly define their collation.

i made a script to change collation once, it worked for my situation, as i remember it it was a monster at 600 lines of code that took all those things into consideration.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 4

Wouldn't creating a new DEV instance with server collation matching that of your production server be easier.

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

As others have pointed out, changing the collation for a database is not simple.

However, if you need to change it it's probably because some comparisons between columns in your database and columns in #temp tables are failing.

I see a couple of options here:

1) Add "COLLATE DATABASE_DEFAULT" to each CREATE #temp TABLE statement and/or directly in the comparison predicate. Not easy, but it will make your application more robust.

2) Change the dev server collation. Obviously, it will be feasible only if you don't have other databases in the dev instance that require a different collation

3) Build a script to change the collation of every column in the restored database, as Lowell suggested.

Lots of pain, no matter which one you choose.

-- Gianluca Sartori