Setting a new collation for SQL Server instance

Collation can be a little bit tricky sometimes. Changing it is not a problem (theoretically) if you want to do it on a column, table or database level – you have to issue respectful ALTER statement and that’s it.

Another thing is changing default server collation. You set it during installation and if you want to change it, you have to rebuild the system databases. Here’s the procedure:

  1. Log on to administrative account.
  2. Run command prompt (as administrator, if you’re on Windows Vista or later).
  3. Go to folder with your SQL Server installation source.
  4. Run the following statement:
    setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=SA_Password SQLCOLLATION=Collation_Name

And that’s it. Tested on SQL Server 2005 and SQL Server 2008 R2. Important thing is that it preserves service pack version (for example you don’t have to reapply SP3 for SQL Server 2005).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s