THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Changing Server Default Collation

An interesting problem develops when the standard default collation for SQL Server isn't appropriate. Working with clients here in the Czech Republic, I have to reset our lab systems server default collation to Czech_CI_AS, and I found it difficult to find out how to do this via Books Online. Since we have systems running SQL 2000, SQL 2005 and SQL 2008, I have to be able to make these changes in each version, and each version has a different method for accomplishing this.

In SQL 2000, you must run rebuildm.exe in the SQL Server binaries directory, and set the collation in the setup dialog.

In SQL 2005, the following command will reset it:

setup.exe /qb INSTANCENAME=[instance] REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=[password] SQLCOLLATION=[new collation]

In SQL 2008, the command is slightly different:

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=[instance] /SQLSYSADMINACCOUNTS=[admin account] /SAPWD=[password] /SQLCOLLATION=[new collation]

Note that you should do this before loading ANY databases or logins. If you've already done so, detach the databases and script the logins using something like sp_help_revlogin (search the Microsoft KB articles for your version). Then, after rebuilding the master database, you can reattach the databases and reload the login accounts.

Collations can be very tricky, and when you're testing application compatibility it's important that issues like this are correctly handled so your testing is valid.

Allen

Published Friday, May 29, 2009 4:23 AM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Saggi Neumann said:

Hey Allen,

It's good that you put all the methods in one place!

You forgot to mention a few things:

1. In 2000, rebuildm requires the installation media. Moreover, you have to copy the files to disk, remove the read-only attribute, and sometimes you have to rename a folder (I forget which one exactly, but it's in the KBs...)

2. All of these set you back at RTM. You must setup SPs and CUs to get to the same build you were on before changing the server collation.

3. If I remember correctly (at least in 2000 and 2005. Haven't had the pleasure with 2008 yet), all system databases are overwritten so all "server level" objects (logins, jobs, endpoints, etc.) are lost so you must back them up in a script. Don't just restore msdb/master/model because then you'll be in the same collation you started with)

Cheers,

S. Neumann

May 30, 2009 2:42 AM
 

cofei said:

I test these on Katmai and passed.

May 31, 2009 5:37 AM
 

Pushpak said:

Thanks...its is really very useful..it worked for me.

August 1, 2011 10:06 AM

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement