x

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

Manikreddy gravatar image

Manikreddy
414 24 26 28

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][1].

[1]: http://msdn.microsoft.com/en-us/library/ms179254.aspx
more ▼

answered Mar 31, 2011 at 05:47 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

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

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x22

asked: Mar 31, 2011 at 05:35 AM

Seen: 1184 times

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