Collation Setting Change In SQL 2005.

The collation of the server is taken as the default collation for all the system databases and the user databases that are created thereafter. Hence by changing the Collation setting for the Server we can create a default collation which would automatically be applied to the new user databases that are created.

Follow the below steps to change the collation for SQL Server 2005:

Take the backup of all the databases, jobs, logins etc.
1. Detach all the user databases.
2. To rebuild the master databse:
* Copy the SQL Bits
* From the command prompt you need to run the setup file. For this you have to change the directory in the command window to the directory where the SQL setup.exe file is located
egs:
C:\> cd d:\SQLBITS\SQL\Enterprise\X86\Tools


* Here you need to run the following command mentioning the new collation that you want to apply:

start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SQLCOLLATION=
Note: Here the command assumes that we are using CD for sql server setup or Install.
Running the above command would lead you to the GUI Screen, similar to the one seen during SQL Installation. Following the instructions on this GUI screen would complete the rebuilding of master database
You may then re-attach the user databases.
Once this is done, you can check the server collation under properties window for a server in Management studio.

Here is the link for reference:
http://msdn.microsoft.com/en-us/library/ms144259.aspx

If we are using dumps for installation :
So open command prompt and browse to the location where sqlserver install dumps are located say D:\X86\Servers\ Now run the below command.

\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD= SQLCOLLATION=

1. SAPWD can be ignored if the sql server authentication is Windows only.
2. Incase if there are user databases and sql server agent jobs configured already then take backup of user database (attach/detach the .mdb and .ldf files) and sysjobs (script the jobs), User logins.

3. Backup the original resource database and system databases, Once the new databases are created with new collation then you can attach back the user databases and script the jobs.