jueves, 22 de marzo de 2012

Change Server Collation

If you want to change Server collation, and tries to execute something like this:

ALTER DATABASE master COLLATE Latin1_General_CI_AI

An error ocurred:

Cannot alter the database 'master' because it is a system database.

The previous instruction only works with non-system database.

To change it in system database, go to console (cmd.exe), and to SQL Server DVD.
Execute something like this.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=YYYYYYY /SQL SYSADMINACCOUNTS=SA /SAPWD= XXXXX /SQLCOLLATION=Latin1_General_CI_AI

INSTANCENAME: your SQL server instance name.
SAPWD: Password of sa (if you choose sa User).
SQLCOLLATION: The collation to change, in this case Latin1_General_CI_AI.

I hope it helps.