Sep
28
2017

Change SQL Server collation of master database

To change server collation following steps needs to be done,

 

Use the SQL Server set up, and run the following command from the folder where set up file is located,

 

Imp Note: this will rebuild master database and subsequently all information like user db, logins, jobs (msdb) will be lost, script out everything as described below before triggering the command.

 

<sql server media folder>:/Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=INSTANAMEHERE /SQLSYSADMINACCOUNTS=DOMAINNAME\SERVICEACCOUNNTNAME /SAPWD= SAPASSWORD /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI (The collation required)

 

Before doing this, please perform the following

 

  1. Script out all the logins
  2. Script out all the jobs
  3. Script out all Linked servers (Password needs to be noted)
  4. Script out any other tasks such as Audits, Traces, replication, Mirroring, AlwaysOn, Log Shipping etc..
  5. Make a note of SQL Server configuration and document it

 

After running the above step SQL Server will present you a blank master/ msdb/ tempdb/ model. You will have to restore the user databases, user logins, jobs and any other tasks.

 

Download PDF

About the Author: Nitin G

Indian born, trekker, biker, photographer, lover of monsoons. I've been working full time with SQL Server since year 2005 and blogs to post the content aquired during my research on new topics or fixing issues faced by me as a DBA while working in different kind of projects, hope some of my posts may helps others in SQLDBA community. Everything you read on my blog is my own personal opinion and any code is provided "AS-IS" with no warranties!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 23 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

October 2017
M T W T F S S
« Sep    
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

View Post by Categories

%d bloggers like this: