Posted by: wajahatabbas | December 6, 2010

Changing SQL Server Collation & Database collation

At present, I am on a SharePoint migration project. In which we have MOSS 2007 32 bit running at production.

Our job is to upgrade it to first 64 bit then to migrate it on SharePoint 2010, and after that making it a medium server farm.

I will share our approach on this, as we use an approach, which minimize the risk, as well as quite easy.

Today, while restoring a Content database to the new production. The database got attached but, it was showing the collation problem. The database at the old DB was using a different collation, and when we restore it to our new DB, it takes the default collation which is different, so it shows error that collation is different.

The easiest way to change the database collation is to run this command

COLLATE Latin1_General_CI_AS_KS_WS

So it changes the database collation. That work, and so you restored the SharePoint Portal.

Now if you want to change the SQL Server collation permanently, then you have to run following command.

D:\sqlserver2008r2setup>setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=MSSQLSERVER /SAPWD=”Om@n18870294″ /SQLSYSADMINACCOUNTS=”mydomain\farmadmin” /SqlCollation=Latin1_General_CI_AS_KS_WS

To check the collation of SQL Server. You can run this sql command ‘sp_helpsort’

I have SQL Server 2008 R2.



  1. But , if i have a wss_content with data and, i change its collation for this database , i’ll have impact troubles for the change ?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s


%d bloggers like this: