If you, like me, have a Test SQL Server that you use to load databases from various SQL Servers, then you may find it useful to change the Default Collation for your server every now and then.
Reason for this is that scripts may fail due to that tempdb and your restored database has different collations. To get the same Collation as your restored database, you need to rebuild SQL Server with the help of your SQL Server Installation CD.
Open up a command prompt and execute:
H:\>setup /ACTION=REBUILDDATABASE /INSTANCENAME=<instance> /SQLSYSADMINACCOUNTS=<your account name> /SAPWD=<your new sa password> /SQLCOLLATION=<new collation>
H:\>setup /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=jacob /SAPWD=strongpassw0rd /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS
Note that your old settings, SQL Logins etc will be deleted!