Changing collation setting on a SQL 2008 instance
Posted by rahmanagoro on August 28, 2010
It is possible to chang the collation setting on a database, but what happens if you try to change the collation setting on a SQL instance or on the system databases, for SQL 2008 this can be done from the command line. Generally, what i tend to do is as follows.
- I will find the location of all my databases, as changing the collation forces a mini re-installation and thus will detach all user databases and rebuild system databases.
select * from master..sysaltfiles
2 A full backup of system databases, msdb, master and model is also recomended. Changing collation will rebuild system databases.
Lets assume that the drive containing the SQL 2008 installation media is E:\
Follow the steps below from the command prompt
cd E: Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="domain\username" /SAPWD= "London2012" /SQLCOLLATION=SQL_Latin1_General_CP1_CS_AS
The parameters are as follows, /SQLSYSADMINACCOUNTS this specifies the sysadmin account nominated.
/SAPWD = This is the SA password to be used afterwards.