Changing Server Default Collation

From:

http://sqlblog.com/blogs/allen_white/archive/2009/05/29/changing-server-default-collation.aspx?CommentPosted=true#commentmessage 

 

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.