If a SQL Server instance has been created using the wrong default collation, it can be a bit of a pain if you try to access the TempDB.
Luckily there is a way to fix this.
1) Logon to the server
2) Backup all the existing database (Note: The collections of existing databases won’t change on the system DBs and any new User database created using the default collation!)
3) Delete/Remove any existing database; this is not specifically needed, but it’s not a bad idea as it will confirm that you have a backup.
4) Open a command window with elevated permissions.
5) Go to the default SQL Server installation directory by typing:
cd C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2
6) Enter the following command:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=WOODGROUP\GG-EPF-AU-BSG-ADMINS "NT AUTHORITY\NETWORK SERVICE" "NT AUTHORITY\SYSTEM" "NT SERVICE\MSSQLSERVER" "NT SERVICE\SQLSERVERAGENT" /SAPWD=<password>.1 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS
Note; /INSTANCENAME=MSSQLSERVER is the name of the default instance
Important: “/SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS” was just chosen at random
7) Restore the backup databases.
