Tuesday, December 11, 2012

How to change server collation in sql server ?


1. Backup all system databases
2. Script all logins and server roles
3. Copy SQL Server binaries into some folder.

Run the below command from command prompt.

4. Start /wait D:\MicrosoftSQL2005EE\setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=abcd1234 SQLCOLLATION=Latin1_General_CI_AS (New Collation Name)
qb - silent installation.
Instance Name - Name of the SQL Instance for which you want to update the collation.
RebuildDatabase - Hence we are rebuilding sql server databses with new collation settings.
SQLCollation -- Specify new collation name.
SAPWD -- SA password for server after rebuild .
It displays setup wizard, Click Yes to overwrite system databases and proceed.
After completion of setup wizard.
1. Open SSMS using new sa password
2. Attach all user databases
3. Execute the logins script
4. Execute the server roles scripts
Note -- This will rebuild all system databases.

No comments: