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.

asked Mar 31 '11 at 05:35 AM in Default

Manikreddy
414 ● 19 ● 25 ● 28

What version of SQL Server?

Mar 31 '11 at 05:40 AM Blackhawk-17

sqlserver 2005(9.0),enterprise edition.

Apr 08 '11 at 01:56 AM Manikreddy

(comments are locked)

2 answers:

Here's the process according to the Books Online.

answered Mar 31 '11 at 05:47 AM

Grant Fritchey ♦♦
61.9k ● 12 ● 19 ● 66

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

Mar 31 '11 at 07:10 AM Fatherjack ♦♦

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

Mar 31 '11 at 07:15 AM Grant Fritchey ♦♦

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

Mar 31 '11 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 '11 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 '11 at 08:13 AM Grant Fritchey ♦♦

(comments are locked)

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.

answered Mar 31 '11 at 08:14 AM