To change SQL Server 2014 Default Instance Collation


I need to change SQL Server 2014 Default Instance Collation and find the Server details.

User Databases : 15
Size of Databases : Below 10 GB  - 10 Databases
                                : 10 GB to 15 GB - 2 Databases
                                : Remaining Three are 36 Gb, 71 GB and 100 GB

Note: Transaction Log files drive have 135 GB free space out of 200 GB.

I have successfully applied following solution on Test Server.

1.      Stop SQL Server Services.
2.      Open a Command Prompt and Navigate to the Binn Directory
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn
 3.      Apply a New SQL Server Collation
sqlservr -m -T4022 -T3659 -s"MSSQLSERVER" -q"SQL_Latin1_General_CP1250_CI_AS"
4.      Start SQL Services and verify collation

Please provide precautions regarding log space during collation change (indexes are restoring during this activity) and more pre-requisites.

Chandra Mohan KanithiSenior Principal Consultant - DatabaseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
Please keep in mind that by just changing the SQL Server Default collation your Database Tables having any "text" like columns may be still in old collation and you will need to change them all to the desired collation by running ALTER TABLE...ALTER COLUMN.
Regarding the Database T-Log size/growth/free disk space...what is/are the database Recovery options? FULL, SIMPLE
If you commit after each change upon completion in SIMPLE recovery mode you should have enough free disk space to make the changes but in FULL or BULK-LOGGED you may need to take some T-Log backups in between changes in order to recycle and reuse T-Log space.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ToddSenior Database AdministratorCommented:

Two ways to change the collation on a database or instance.

As Icohan mentioned above it involves changing each and every text field in the database in question - ie create a new database with the correct collation and schema with correct collation and then transfer all the data as necessary.

Or one I got shown - if the change is merely between different English collations - is to change the collation from the command line.

I've done this on one set of servers (dev/test/prod) and all seem okay a year later.

Chandra Mohan KanithiSenior Principal Consultant - DatabaseAuthor Commented:
Thanks for prompt response.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.