SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database collation Change


Database collation Change

Author
Message
bspavan86
bspavan86
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 50
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)
HanShi
HanShi
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14530 Visits: 3765
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
free_mascot
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12127 Visits: 2250
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
Lowell
SSC Guru
SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)

Group: General Forum Members
Points: 138945 Visits: 41521
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
crmitchell
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1903 Visits: 1920
Wouldn't creating a new DEV instance with server collation matching that of your production server be easier.
Gianluca Sartori
Gianluca Sartori
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43415 Visits: 13367
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search