Change sql server collation

Question

Change sql server collation

WhiteLotus

SSChampion

Points: 12122
More actions
February 28, 2017 at 6:12 pm

#341084

Hi Guys ..
I would like to change sql server collation . Can I just reinstall sql and change the collation during the installation ?
Or is there anything that i need to pay attention ?
Many thankss
Cheers...
March 1, 2017 at 10:34 pm

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Yes, you can do that. Make sure you back up all your user database first, and script out important system stuff such as jobs and logins. Bear in mind that when you've put your users databases back on to the server with the new collation, those databases will have the same collation as before and therefore you may get collation conflict errors if you use temp tables in your code.

John

tim.ffitch 25252 SSCrazy Points: 2055 More actions · Answer 2

The is an undocumented engineering script you can use to change the collation on an entire server, which includes all databases, all fields and all indexes. It's also very quick. I have used this a few times myself. It works as long as the collation you are changing too is different to the current system databases. So for example if you had just restored a database to the server that was Latin1_General_CI_AS but the server was SQL_Latin1_General_CP1_CI_AS and you wanted to change the database to match the server you would have to run this twice. Once to change the server to Latin1_General_CI_AS and then again to change everything to SQL_Latin1_General_CP1_CI_AS. This is a godsend, it saves hours and hours of work.

1.       From a command prompt run: (making sure the instance name is correct)
NET STOP "SQL Server (MSSQLSERVER)"
2.       Confirm Y at the command prompt that you want to stop the services.
3.       From the command prompt run: (making sure the instance name is correct, and changing the collation name to suit).
sqlservr -m -T4022 -T3659 -s"MSSQLSERVER" -q"SQL_Latin1_General_CP1_CI_AS"
4.       At the end of the sequence you should receive the messages: “The default collation was successfully changed and Recovery is complete” and “This is an informational message only. No user action is required”.
5.       At the command prompt type Ctrl + C to shutdown SQL Server and then type Y to confirm you want to shutdown SQL Server
6.       Now restart the SQL Server and SQL Server Agent Services

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

WhiteLotus - Tuesday, February 28, 2017 6:12 PM
Hi Guys ..
I would like to change sql server collation . Can I just reinstall sql and change the collation during the installation ?
Or is there anything that i need to pay attention ?
Many thankss
Cheers...

The supported route is to run the installer and rebuild the system databases, you will need to restore any user databases or objects like jobs, logins, etc.

General steps are;

If the instance is a clustered instance, offline the cluster group using the following PowerShell cmd
```
stop-clustergroup -name thegroupname
```

Launch setup.exe either from the install DVD or from the local cache folder. Valid locations are amongst the following

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release

Once you have set the correct path in the DOS command prompt, use the following command line. If instance is mixed mode, sa pwd is required

setup.exe /quiet /action=rebuilddatabase /instancename=theinstancename /sqlsysadminaccounts="domain\a user or group" /sqlcollation=SQL_Latin1_General_CP1_CS_AS /SAPWD=strong password

Once the installation has completed, online the cluster group using the following PowerShell cmd
```
start-clustergroup -name thegroupname
```
Check the collation using the following T-SQL cmd
```
SELECT SERVERPROPERTY('Collation')
```
Reinstall your objects

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

JasonClark - Wednesday, March 1, 2017 10:33 PM
WhiteLotus - Tuesday, February 28, 2017 6:12 PM
Hi Guys ..
I would like to change sql server collation . Can I just reinstall sql and change the collation during the installation ?
Or is there anything that i need to pay attention ?
Many thankss
Cheers...
There is no need to reinstall. You may also change this on installed SQL Server. Have a look: https://www.mssqltips.com/sqlservertip/2901/how-to-change-server-level-collation-for-a-sql-server-instance/
Hope, this will help you..................

same as what i posted then

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉