Change the Server Collation of SQL Server 2005

by jasonstrate, posted in SQL Server

A nice little change in SQL Server 2005 has been the ability to change the collation of the server.  Back in the day, circa SQL 2000, changing collation and reinstalling SQL Server were pretty much the same thing.  By pretty much, I mean the exact same thing.

In SQL Server 2005 you can now do this in a few easy steps, the following is from MSDN:

  1. Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.
  2. Export all your data using a tool such as bulk copy.
  3. Drop all the user databases.
  4. Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:
    start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI
  5. Create all the databases and all the objects in them.
  6. Import all your data.

Well, the steps aren’t exactly easy and it might be a lot easier to install SQL Server with the correct collation to begin with.  But when a friend calls with this questions, this is the answer.