Avatar of jana
jana
Flag for United States of America asked on

Change collation of sql2017 when freshly installed.

We have installed a fresh new SQL 2017 server but has wrong collation; what's the proper steps to successfully change the collation to the desired one?
Microsoft SQL Server

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
David Johnson, CD

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
jana

ASKER
Yes, since it’s a new install and has no production databases just the database installed by the SQL install, we don’t need to rebuild right?

The databases that gets installed with a fresh new SQL don’t need rebuild?

If change collation as recommended by David, we will not lose any configurations, logins and any other server objects like linked servers?
Vitor Montalvão

Yes, it will need a rebuild so yes, you'll lose all configurations from the SQL Server instance.
User databases have their own collation (usually from the SQL Server instance where they were created) but changing a database collation only affects the current database and not the SQL Server instance.
jana

ASKER
Understood, can you provide some link to full rebuild of the system databases.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
David Johnson, CD

@victor the commands given do not require a rebuild

A few days ago, I came across an issue where we had to change the SQL server instance collation and we were just asking the clients to rebuild the system databases to do so in the previous cases. But this time the client could not afford to do that and asked for a plan B.
jana

ASKER
Ok.  Note the new sql install has no working database, just the system database that it install with a new installation.
Vitor Montalvão

@David, I read that article but it's hard to me to believe that a SQL Server instance collation change doesn't need a rebuild. Of course rayluvs should give a try to confirm that. Unfortunately I don't have any available SQL Server instance to play with the -q parameter (btw, I checked in MSDN and couldn't find that parameter. It might be one of the hidden ones).
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Vitor Montalvão

raylus, can you tell us if the SQL Server instance has been rebuilt or not?
I would like to confirm that the -q parameter doesn't rebuild the SQL Server instance, i.e., you don't loose any server object during the process.
jana

ASKER
No (seems to work fine)
Vitor Montalvão

Thank you for the confirmation.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy