collation change of sqlserver?

Can you please let me know how to change the collation of the sever as I missed to change the default collation at the time of istallation.?

Thanks in Advance.

more ▼

asked Mar 31, 2011 at 05:35 AM in Default

avatar image

414 25 26 31

What version of SQL Server?

Mar 31, 2011 at 05:40 AM Blackhawk-17

sqlserver 2005(9.0),enterprise edition.

Apr 08, 2011 at 01:56 AM Manikreddy
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Here's the process according to the Books Online.

more ▼

answered Mar 31, 2011 at 05:47 AM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

not at all scary. "drop all user databases". gulp.

Mar 31, 2011 at 07:10 AM Fatherjack ♦♦

Probably best to think about this when setting up the server.

Mar 31, 2011 at 07:15 AM Grant Fritchey ♦♦

yeah, for sure. I am limping with one server that got it wrong here!

Mar 31, 2011 at 07:35 AM Fatherjack ♦♦

Well, at least there are some bright spots:

  • The syntax to drop all user databases is pretty straightforward :)

  • There is an option in SSMS to script data along with the objects though I am not positive about how it scales :(

  • According to the Note section in the BOL article:

Instead of changing the default collation of an instance of SQL Server, you can specify a default collation for each new database you create

The latter is probably the safest bet.

Seriously though, one thing that is somewhat difficult to figure out is the step to rebuild the master database with new collation. I mean that all newly created databases are based on the settings in the model database plus a bunch of system objects from the master and resource, so it would appear that the model settings have to be somehow involved in the process, but nope, rebuilding just the master does it.

Mar 31, 2011 at 08:05 AM Oleg

Well, you could always get a Red Gate SQL Compare bundle to handle the import & export of the structures and data.

Mar 31, 2011 at 08:13 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

For me I'd use something like RedGate SQL Compare and SQL DataCompare to do the scripting and data move. You would need a new server (possibly only temporary) configured how you want it and ship all data over to that.

more ▼

answered Mar 31, 2011 at 08:14 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 31, 2011 at 05:35 AM

Seen: 1325 times

Last Updated: Mar 31, 2011 at 05:35 AM

Copyright 2018 Redgate Software. Privacy Policy