Post reply

Need to change SQL Server Collation level

  • 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.

  • May 24, 2012 at 7:39 am

    #1492533

    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.

  • May 24, 2012 at 8:52 am

    #1492615

    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.

  • May 24, 2012 at 8:57 am

    #1492619

    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?

  • May 24, 2012 at 9:09 am

    #1492627

    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

  • May 24, 2012 at 9:09 am

    #1492628

    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??

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

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