Change the Collation for the Instances in SQL Server 2008R2


During the installation, I accidently used the the default collation, I actually wanted to use Latin1_General_CI_AI for the default instance and Instance2 on the  x64 Win 7 machine. I do not want to go through the time-consuming uninstall and re-install process, for just changing the collation. So I chose the option to rebuild the system databases. Then change the collation as follows.


Issued the following command with the admin priviledge:

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLServer

NOT WORKING! Using the Installation path, it worked:


For the 2nd Instance named Instance (notice NOT "MachineName\Instance2" or others, just Insatnace2):

H:\downloads\SQL Server 2008 R@ Developer Edition\Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=Instance2

Not surprisingly, the AdventureWorks sample databases are gone, I need to restore them.

However, when I tried to re-install them, the sample databases not showing up. Deleted the mdf data files, tried again, showed some DBs, not all of them. Finally, removed the files from .../Data and .../tool/sample (created for the installation in the early process) folders, restore them again, Aha!.