Ask the Community
Groups
Change Master Collation - Connect IT Community | Kaseya
<main> <article class="userContent"> <p><strong>Initial Situation</strong></p> <p> </p> <p>We have the Master DB with a specific collation and we want to change this (same as ksubscriber)</p> <p>"SQL_Latin1_General_CP1_CI_AI" or "Latin1_General_CI_AS"</p> <p>In this case, I would like to change Latin1_General_CI_AS ==> SQL_Latin1_General_CP1_CI_AI</p> <p><img src="/attachments/token/FpPE7LQOShBjlQ4z1dBKLWU6Z/?name=2015-11-25_1141.png" alt="2015-11-25_1141.png" class="embedImage-img importedEmbed-img"></img></p> <p> </p> <p> </p> <p>First we need to launch CMD with administrator permissions</p> <p> </p> <p><img src="/attachments/token/B2UIFte6px32LRdhAOrj5QQhw/?name=2015-11-25_1449.png" alt="2015-11-25_1449.png" class="embedImage-img importedEmbed-img"></img></p> <p> </p> <p>Navigate to Windows\System32 and execute : sc queryex type= service state= all | find /i "SQL Server"</p> <p>Under SQL instance present in DISPLAY_NAME : SQL Server you will find the name.</p> <p>Next we need to navigate in the folder where the query will be executed</p> <p> </p> <p>C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Binn</p> <p> </p> <p>to be sure that this is the correct folder we can check if the sqlserver.exe is present with the command " dir *.exe"</p> <p>we should find the sqlservr.exe</p> <p> </p> <p>Now we are in the correct folder we must stop the sql server</p> <p><img src="/attachments/token/5pUjAaAAU6rdDptSTuslRGvUq/?name=2015-11-25_1400.png" alt="2015-11-25_1400.png" class="embedImage-img importedEmbed-img"></img></p> <p> </p> <p>and we execute the modification query :</p> <p> </p> <pre class="code codeBlock" spellcheck="false" tabindex="0">sqlservr -m -T4022 -T3659 -s"NAME OF YOUR INSTANCE" -q"NEW COLLATION NAME"<br><br></pre> <pre class="code codeBlock" spellcheck="false" tabindex="0">sqlservr -m -T4022 -T3659 -s"SQLEXPRESS" -q"SQL_Latin1_General_CP1_CI_AI"</pre> <pre class="code codeBlock" spellcheck="false" tabindex="0"><br><br></pre> <p><img src="/attachments/token/DAhoxphCZE1BUV2sgvsnKjNbP/?name=2015-11-25_1425.png" alt="2015-11-25_1425.png" class="embedImage-img importedEmbed-img"></img></p> <p> </p> <p>after this we restart the SQL server and the Master DB should have the new collation</p> <p> </p> <p> <img src="/attachments/token/uHUYFQ23FjVooGYJRM7klA5cc/?name=2015-11-25_1441.png" alt="2015-11-25_1441.png" class="embedImage-img importedEmbed-img"></img></p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> </article> </main>