Sometimes we need to change Collation after we have done SQL Server Installation.
I will do my warning from the beginning. After changing Collation, everything in the master database is reset.
So you will have a new installation. So if you have any databases on the Instance you are going to change Collation, or if you have defined a user, be sure to get this information backed up.
Also, you should not do this operation on a running system.
Right-click on the cmd command line and run it with Run As Administrator.
Then you go to the path where the SQL Server setup file is located and run the following command.
For example, our setup file is in the C:/Kurulum folder.
You can come to that path as follows.
You can type “cd C:\Kurulum” to directly go to the path.
/INSTANCENAME | Whatever your instance name is, you should write it here. If you are using the default instance you should type MSSQLSERVER. If you are using Named Instance; You should type “ServerName\InstanceName”. |
/SQLSYSADMINACCOUNTS | The user with sysadmin authority to perform the operation. It can be a domain user, or a sql user. |
/SAPWD | The user’s password that you have defined |
/SqlCollation | You should enter your new Collation information here. |
1 | setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=default /SQLSYSADMINACCOUNTS=User with sysadmin authority /SAPWD=your password /SqlCollation=Turkish_CI_AS |
For Example;
1 | setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=SQLServer01\TESTINSTANCE /SQLSYSADMINACCOUNTS=dbauser /SAPWD=QWERasdf1234 /SqlCollation=Turkish_CI_AS |