Changing the collation on an existing SQL Server 2008 instance

If you have a SQL Server 2008 installation and wish to change the collation, you can run the following:

setup.exe /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=MyDomain\MyAccount /SQLCOLLATION=SomeCollation

Where “MSSQLSERVER” is the default instance name (you can change this to a named instance) and the rest of the parameters are fairly self explanatory.

Note that user databases will not be updated, only the system databases (Master etc).

To update any user databases you need to:

• Export all data from user databases using something like the BCP utility.
• Drop all user databases
• Update collation using above command
• Create user databases
• Import the data that you exported

Note that a back-up and restore of user databases does not work. In that scenario you simply restore the previous collation. You need to create an entirely new database and import the data.