Need to change SQL Server Collation level

Question

Need to change SQL Server Collation level

Jinu43

Ten Centuries

Points: 1237
More actions
May 24, 2012 at 7:28 am

#274432

Hi,
I have to change the Collation level setting for existing SQL Server 2000.
In this server i have around 20 database which all have collation level "SQL_Latin1_General_Pref_CP437_CI_AS" and SQL server 2000 has it's default collation level which is different the current database collation level.
I need to change the serevr level collation and make it same as database collation which is "SQL_Latin1_General_Pref_CP437_CI_AS".
I came across below article doing search on this issue.
http://msdn.microsoft.com/en-us/library/aa197950%28v=sql.80%29.aspx
I don't have much experince with SQL 2000 administration, so i am looking for some guidence here.
Please give me some guidence or issue i can face during this change.
Thanks in advance for your Help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

What is the business requirements on changing the server level collation? Is there a specific problem you are attempting to fix?

Rebuilding the master DB will result in all the databases being detached, so you will need to manually attach them all, and all server wide objects like logins, linked servers etc etc will be lost as it is a completly brand new master DB and starts from scratch, so ensure you have all of the objects scripted or know the definitions of the objects before proceeding.

Jinu43 Ten Centuries Points: 1237 More actions · Answer 2

They just migrate this server from another data centre and now the serevr collation is diferent then database which causing some issue on coding side.

I will be doing below step in maintance time window for this fix.

- Backup the all the database.

- Script out SQL Jobs and Login for all DB.

- Dettach all User DB

- Rubild the master DB as below article.

http://msdn.microsoft.com/en-us/library/aa197950%28v=sql.80%29.aspx

- Attach all User DB

Please let me know if i miss something here(Let me know if you have any other way to rebuild the master database in SQL 2000).

Can you please let me know - Rebilding a master DB will affect the SSIS packages or SQL Jobs or not ?? which is part of MSDB database.

Thanks for all your help.

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

SQL 2000 is DTS not SSIS, are you sure its SQL 2000 or is it SQL2005/2008?

What coding issues are you facing?

Rebuild master just rebuilds the master database, but to be on the safe side make sure you have a backup of MSDB.

Also are you sure you only need to script out logins, do you not have any other server level objects which need to be recreated?

heymiky SSCarpal Tunnel Points: 4875 More actions · Answer 4

you mention that the databases were migrated from one data center to another, was this to a clean install of SQL Server? If this instance was alread in use i'd reccomend you spin up another instance and use that or you could have issues with existing DB's

Jinu43 Ten Centuries Points: 1237 More actions · Answer 5

Sorry it is my mistake, not SSIS but it has DTS Packages.

I will take the System Database backup as well.

This Serevr has linked servers / Database maintenace plan /DTS packages, except that i didn't find any server level objects on this server.

Thank You very much for pointing out Linked serevr which i missed on my steps.

I will script out Linked server as well.

Any other thing i need me make sure before i start??