How to change MS SQL Server 2005 server collation

The most clear and easy way is to completely reinstall the sql server instance. In SQL server 2000 and earlier versions there is rebuild master utility – rebuildm.exe which has very clear interface but there are many cases when this utility hangs and you lose your master database forever.

Starting from SQL server 2005 rebuild master utility is integrated into installation package. So you need to load your installation drive and run from command line the next command

setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=MSSQLSERVER

/SAPWD=”password” /SQLSYSADMINACCOUNTS=”BUILTIN\ADMINISTRATORS”

/SqlCollation=Latin1_General_CI_AS

Adjust this params for your needs – INSTANCENAME – put the instance name you want to change collation of. INSTANCENAME=MSSQLSERVER – default instance. SAPWD – new sa password, SqlCollation – new sql server instance collation. This query may be used to rebuild master of SQL server 2005 or to rebuild master of SQL server 2008

Consider that after rebuild you will have a fresh SQL server instance. You will need to attach or restore your databases, recreate logins and so on.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s


%d bloggers like this: