Resetting Collation in SQL Server 2005

I accidentally installed a development version of SQL Server 2005 with a case-sensitive collation. This upset other SQL databases I was working with that were not expecting table names and etc. to be case sensitive. In SQL Server 2005, you can reset the collation by using setup with the REBUILDDATABASE parameter.

Details about this process are here.  Details about setting the collation are here.

The following command (one line) worked for me:

start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER
     REINSTALL=SQL_Engine
     REBUILDDATABASE=1
     SAPWD=password
     SQLACCOUNT=doman\user SQLPASSWORD=password
     SQLBROWSERACCOUNT=doman\user SQLBROWSERPASSWORD=password
     SQLAGTACCOUNT=doman\user SQLAGTPASSWORD=password
     SQLCOLLATION=Latin1_General_CI_AS_KS_WS
     ASCOLLATION=Latin1_General_CI_AS_KS_WS

(I originally tried this from the “\Program Files\Microsoft SQL Server\90\Setup Bootstrap” directory, but this generated an error saying an .MSI file was not found. But it worked fine from the original installation media.)

However, there was a problem trying to reapply SP2 (found here). There have been additional updates since SP2 was released (described in detail here), which Windows Update had already applied. Therefore, I could not apply the SP2 updates since components were already at a later version.

From here, I assume you have two options:

1. Uninstall SQL Server altogether, reinstall, and apply the service pack. This, of course, defeats the purpose of using the REBUILDDATABASE option in the first place.
2. Request the cumulative service pack update (described here) and apply it.

I ended up going with the first option.

Advertisement