Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

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.

  1. 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.

Advertisements

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: