Change SQL Server default collation

MS SQL Server 2000
Before changing the default collation on SQL Server check that you did not create any user databases on the server. It may create problems in future if there is already one or more user databases on the server.
To change SQL Server collation the master database has to be rebuilt. To rebuild the master database:

  1. Obtain the copy of SQL 2000 Server installation CD.
  2. Stop SQL Server service.
  3. Run Rebuildm.exe. (This is located in the Program FilesMicrosoft SQL Server80ToolsBinn directory.)
  4. In the Rebuild Master dialog box, click Browse.
  5. In the Browse for Folder dialog box, select the Data folder on the SQL Server 2000 compact disc and then click OK.
  6. Click Settings. In the Collation Settings dialog box, verify or change settings used for the master database and all other databases.
  7. Initially, the default collation settings are shown, but these may not match the collation selected during setup. You can select the same settings used during setup or select new collation settings. When done, click OK.
  8. In the Rebuild Master dialog box, click Rebuild to start the process.
  9. The Rebuild Master utility reinstalls the master database.
  10. Start the SQL Service and check the default collation.

MS SQL Server 2005

Before changing the default collation on SQL Server check that you did not create any user databases on the server. It may create problems in future if there is already one or more user databases on the server.
To change SQL Server collation the master database has to be rebuilt. To rebuild the master database:

  1. Obtain the copy of SQL 2005 Server installation CD.
  2. Stop SQL Server service.
  3. Start the SQL Server in single user mode. This can be done by running the command <install drive>:Program FilesMicrosoft Sql ServerMSSQL.#MSSQLBinnsqlserver.exe -m
  4. Rebuild the master database: Open command prompt. Run start /wait <SQL installation CD>:Serversetup.exe /qn INSTANCENAME=<instance> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<New SA Password>”.
  5. Start the SQL Service and check the default collation.