03 February 2016

Change SQL Server Collation @ Instance Level

To change the collation at the instance level we need to follow below steps:

1. Go to Setup.exe path, this will be normally as follows:
     C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\

2. Take the SQL Services Offline.

3.  Go to command prompt and choose above location:

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME= (INSTANCE NAME) /SQLSYSADMINACCOUNTS= (SVC ACC) /SAPWD=(SAPWD) /SQLCOLLATION=SQL_Latin1_General_CP850_BIN 

Instance name:

To verify the instance name  use below query
select SERVERPROPERTY('instancename')

SVC Acc:

Use service account or any domain account which has full permissions on email.

SAPwd:

If its a mixed authentication mode then we need to use SA pwd to complete the rebuild.

4. Check the summary log once its completed for verification, we can verify under instance properties also.

Imp Note: Please take system db backups to restore once we complete rebuild. Else we will loose all SQL Server jobs and Logins.