Jun
29
2012

Changing SQL Server Collation / Rebuilding system databases

You may find yourself in situation where you need to change Server level collation or need to rebuild system databases, then following article will be helpful,

1. First detach all user databases
2. Script any logins (if exists) using sp_help_revlogin (get one from MSDN for e.g. http://support.microsoft.com/kb/246133 , http://support.microsoft.com/kb/918992
3. Script Maintenance plans/Jobs (if can’t be created again) 4. Execute following on command line by going to the folder where appropriate setup of SQL Server is present,

In SQL 2000, you must run rebuildm.exe in the SQL Server binaries directory (C:\Program Files\Microsoft SQL Server\80\Tools\Binn\), and set the collation in the setup dialog.


In SQL 2005, the following command will reset it:

setup.exe /qb INSTANCENAME=[instance] REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=[password] SQLCOLLATION=[new collation]


In SQL 2008/R2, the command is slightly different:

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=[instance] /SQLSYSADMINACCOUNTS=[admin account] /SAPWD=[password] /SQLCOLLATION=[new collation]

Where,
[instance] = If default instance then put MSSQLSERVER else put instance name only
[admin account] = Specify the Windows groups or individual accounts who need admin access for e.g. add domain group etc as Domain\Accountname (note: adding more than one account, put space in between)
[password] = Specify strong sa password (should be of 9 characters with lower and upper caps including numbers and special characters)
[new collation] = Specify the new collation for e.g. Chinese_PRC_CS_AI

5. After rebuilding the master database, you can reattach the user databases and reload the login accounts.

Collations are tricky and when you’re testing application compatibility it’s important that issues like this are correctly handled so your testing is valid.

Source: http://sqlblog.com/blogs/allen_white/archive/2009/05/29/changing-server-default-collation.aspx
References:
http://msdn.microsoft.com/en-us/library/dd207003(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms179254(v=sql.105).aspx

Download PDF

Related Posts

About the Author: Nitin Garg

Indian born, trekker, biker, photographer, lover of monsoons... I love to blog the topics I research and find useful for self or online community to save time and energy :) Everything you read on my blog is my own personal opinion!

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 195 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

August 2019
M T W T F S S
« Nov    
 1234
567891011
12131415161718
19202122232425
262728293031  

View Post by Categories

%d bloggers like this: