Changing server collation

How to change server collation

    The most clear and easy way is to completely reinstall the sql server instance. In sql server 2000 and earlier versions there is rebuild master utility - rebuildm.exe which has very clear interface but there are many cases when this utility hangs and you lose your master database forever.

   Starting from sql server 2005 rebuild master utility is integrated into installation package. So you need to load your installation drive and run from command line the next command

setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=MSSQLSERVER /SAPWD="password"

/SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /SqlCollation=Latin1_General_CI_AS

(type this command in a single line in your cmd console)

Adjust this params for your needs - INSTANCENAME - put the instance name you want to change collation of.  INSTANCENAME=MSSQLSERVER - default instance. SAPWD - new sa password, SqlCollation - new sql server instance collation. This query may be used to rebuild master of sql server 2005 or to rebuild master of sql server 2008

Consider that after rebuild you will have a fresh sql server instance. You will need to attach or restore your databases, recreate logins and so on.

How to change database collation

    To change the database collation you can use  alter database operator. For instance to change collation of previously created database collationtest you can run the script

alter database collationtest collate Cyrillic_General_CI_AS

Script that returns all your databases' collations

select name, collation_name

from sys.databases

 Query that returns your sql server instance's collation

select SERVERPROPERTY('collation')