Change default Collation in SQL Server 2008 R2

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>

Example:

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!

This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *