Change SQL Server Collation without rebuilding system databases | SansSQL

Friday, October 4, 2013

Change SQL Server Collation without rebuilding system databases

How many of you want to change the SQL Server Collation without rebuilding the system databases?

In this post, I will explain a method using which the server collation can be easily changed and is through a undocumented start up parameter.
Since this is undocumented, I highly recommend to use this with extra care and with your own risk.

These method uses -q startup parameter while starting the SQL Server service.

Method 1:
  1. Detach all user databases
  2. Make backup of all other required database and server objects
  3. Stop SQL Server Service
  4. Start the SQL Server Service using command Prompt
    net start "SQL Server (<Instance Name>)" /m /T4022 /T3659 /q"<New Collation Name>"
  5. Re-start (Stop and Start) the SQL Services Normally without any startup parameters
  6. Check for the change in Server Collation
  7. Attach the user databases back

Method 2:
  1. Detach all user databases
  2. Make backup of all other required database and server objects
  3. Stop SQL Server Service
  4. Start the SQL Server Service using command Prompt
    sqlservr -m -T4022 -T3659 -q"<New Collation Name>"


  5. Now check for the message "Recovery is completed" and then press CTRL+C to Stop the SQL Server service
  6. Start the SQL Services Normally without any startup parameters
  7. Check for the change in Server Collation
  8. Attach the user databases back
Note:
  1. If you do not detach the user databases before changing the server collation then the process will change the collation for all the databases.
  2. Trace flag 3659 allows logging all errors to sql server logs
  3. Trace flag 4022 forces SQL Server to skip startup stored procedures (if any)
  4. Startup Parameter “-m” forces single user mode.
  5. Startup Parameter  -q” rebuilds all databases and objects to the specified collation, without reinstalling the instance or rebuilding system databases.

8 comments:

Unknown said...

So I think you need to amend the name of this post because this DOES change the system databases, you can even see that it is doing in your Method 2 screenshot ffs...!!

Unknown said...

I am very depressed by seeing the large number of rows and columns of the data which is on tabular form and my boos tells me to convert this big data into very short and summarize from in very short time, so my friend suggest me to use the data scientist and form this activewizards.com I hire data scientist form my work and my work done in very short time.

Unknown said...
This comment has been removed by the author.
Unknown said...
This comment has been removed by the author.
Unknown said...

I followed both approaches, however, it does not change collation of system databases at all for me. I am using SQL SERVER 2016.

Please help me. Currently, it is SQL_Latin1_General_CP1_CI_AI. I want collation to be SQL_Latin1_General_CP1_CI_AS for all master databases.

Unknown said...

Thanks. I followed method 1 and it worked fine in sql server 2008 r2 but not in sql server 2016. In 2016 server, In front end it is showing the updated/new collation type but if i check it through script(SELECT SERVERPROPERTY(N'COLLATION')), it is still showing the old collation name.

Huongkv said...

Aivvu chuyên vé máy bay, tham khảo

vé máy bay đi Mỹ giá rẻ

vé máy bay tết

kinh nghiệm mua vé máy bay đi Canada

săn vé máy bay đi Pháp

vé máy bay đi anh bao nhiêu tiền

tra vé máy bay giá rẻ

combo khách sạn đà nẵng

combo nghỉ dưỡng nha trang

Huongkv said...

Aivivu chuyên vé máy bay, tham khảo

vé máy bay đi Mỹ bao nhiêu

giá vé máy bay từ mỹ về vn

các chuyến bay từ đức về việt nam hôm nay

giá vé máy bay nga về việt nam

Post a Comment

Ads