Categories
Database

SQL Server 2008 Express: Change Server Collation

If you managed to get in trouble as I did, because you have different database and server collation (yes, there is a difference between server and database collation) and you use SQL Server 2008 Express, then you’ve found the right place to solve your problem.

How to get the setup.exe mentioned all over internet for standard SQL Server 2008:

  1. Start the SQL Server Express installer
  2. Wait until the extraction dialog is closed and you can see the installation window
  3. The installer will extract all the setup files into c: drive, the directory name can be easily found by looking for a mess of digits and letters
  4. Copy the content to a different directory (when you close the installer the content extracted by the installer will be removed)
  5. Close the installer (because there can’t be 2 installers running at the same time)
  6. Now you have the setup.exe available and you can run the old good rebuild command
Rebuild SQL Server 2008 database with new server collation and sa password:

setup.exe /Quiet /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SAPWD=[newpd] /SQLSYSADMINACCOUNTS=[machinename]\Administrators SQLCOLLATION=SQL_Latin1_General_CP1_CS_AS

 

where:

[nepwd] is new sa account password

[machinename] is the machine name to use the administrators group from

 

PLEASE NOTE:

You have to reattach your databases and recreate the logins, e.g. by running:

EXEC sp_change_users_login ‘Auto_Fix’, ‘user’, ‘login’, ‘password’