4

How can i change server collation without changing whole databases, because i have many databases which aren't belong to me, so i want change server collation and my database only.

SQL Server version: 2016 SQL Server Collation: Arabic_CI_AI

in the server i have many databases, some of these databases i work on them and others are not belong to me and should not be touched. I want to change the collation: Arabic_100_CI_AI

When i use this script: 

sqlservr -m -T4022 -T3659 -s"MSSQLSERVER" -q"Arabic_CI_AI"

it change the server collation along with all databases in the server, but i don't want this i need to do change only the collation of the server only.

Is that possible?

Fares Ayyad
  • 141
  • 3

3 Answers3

4

You'll need to run setup with the REBUILDDATABASE option to change the server collation without changing the collation of existing user databases. This will create fresh system databases so you'll need to re-create server-level objects, re-attach user databases, and remap logins afterwards.

Note the tempdb collation will also be changed and can result in collation conflict errors after the operation depending on the existing user database collations and how queries are coded (e.g. creates temp tables without an explict DATABASE_DEFAULT column collation specified).

A system collation change is not a trivial matter and needs to be thoroughly tested.

Dan Guzman
  • 28,989
  • 2
  • 46
  • 71
3

If you need to do change of settings at server level without affecting other databases, I would suggest you to create a new instance on the same server. If you are running on default instance then, create a named instance and transfer your databases to this instance. This way, you can do the changes as per your wish without affecting other databases.

In case, above is not possible then, its really difficult and has potential to impact all databases as already mentioned by Dan Guzman.

I hope above helps.

Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320
Learning_DBAdmin
  • 3,924
  • 19
  • 39
1

To change the collation of the instance and 0 or more of the user databases, you can use the undocumented -q method that you are already doing, and simply detach 1 or more databases that you want to exclude from the collation update. Then, after the collation update, reattach all of the databases that you detached.

As compared to the documented method of running SETUP.EXE /ACTION=REBUILDDATABASE, this approach doesn't require rebuilding SQL Agent jobs, logins, etc. But the drawback is that it is undocumented / unsupported. This is not to say that anything will go wrong, just that if something does, Microsoft won't help fix it.

For a detailed description of what the sqlservr.exe -q method does (and loads of details on how collations work at the various levels and potential issues to watch out for), please see my post:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

I even mention this scenario of updating only a subset of the databases using detach / attach in that post.

Solomon Rutzky
  • 70,048
  • 8
  • 160
  • 306