May 12, 2008 at 12:25 pm
A consultant in our company installed an SQL 2005 with case-sensitive collation.
Because of messy system here, I found it when it was passed over to me. Now, I would like to change the collation to case-insensitive. However, all system databases are full of "components", such as logins, stored procedures, packages, assemblies, ....
What will you suggest me to do for this "recovery"?
Many thanks for any input in advance.
May 12, 2008 at 6:52 pm
Your only option is to rebuild the system databases in order to change the server level collation. Before commencing it you can script all the logins using the below MS KB article,
http://support.microsoft.com/kb/918992
You need to script all the jobs as well by right click > generate script etc..
Refer the below article for rebuilding system databases in Sql 2005,
http://sql-articles.com/index.php?page=articles/rebuildsysdb.html
[font="Verdana"]- Deepak[/font]
May 21, 2008 at 3:58 pm
Thank you for your input.
How about the packages, assemblies, ... in the system databases?
May 22, 2008 at 1:13 pm
In order to come out from the messy data. You can Install new instance and restore all DB's and then applly changes as you wish. I assumed it is a PROD server. You may not have to do the rebuilding the whole server because of the messy.
Once you done with all changes you may need to uninstall the old inctance and change in the existing inventory with new instance.
That is the only option you have. Rebuilding the current PROD is not advisale.
MCP, MCTS (GDBA/EDA)
May 22, 2008 at 2:22 pm
this is not good at all..
i dont think there is clean solution.
u have to script out every thing u need. which is stored in the sys databases.
..>>..
MobashA
May 22, 2008 at 2:55 pm
I agree with mobasha's comment. At this moment, I have not got any clean solution since I post this question a week ago.
Also, I would like to show my thanks for Deepak's careful reply.
The problems in this scenario are summarized as below:
1. All system databases were created in another collation. Their backups cannot be used.
2. What data should I retrieve before rebuilding these system databases? The data should include security, jobs, packages, assemblies, ... whatever you can think about.
3. How to retrieve the data?
Once again, many thanks for any input.
May 22, 2008 at 6:36 pm
You can save your packages as .dts file and then later import them into Sql Server. Regarding the assemblies I am not sure if you can script it. As Mobasha suggested you need to script all the objects before rebuilding.
[font="Verdana"]- Deepak[/font]
May 22, 2008 at 10:10 pm
I agree with scripting all Onjects before rebuilding, but what about data. once you generate the script, it wont come with Data on the DB or any object.
Does it make sence?
MCP, MCTS (GDBA/EDA)
May 23, 2008 at 2:04 pm
Before you go through the trouble of scripting everything out make sure that your data is compatible with case insensitivity. You need to check all primary keys, and unique indexes to make sure you can re-load the data.
Going from case insensitive to case sensitive is a relatively painless issue as far as the data is concerned. The converse is not.
'texas' and 'Texas' are 2 seperate entries in a case sensitive database and will show up in different places in a sorted list.
May 23, 2008 at 2:52 pm
I would recommend to with new Installation of new instance and move the things from old to new sever then make changes what you wnat to do?
MCP, MCTS (GDBA/EDA)
May 23, 2008 at 3:15 pm
hmmm...ok we do not have clear situation!?
A B
=========================
SQL ORACLE Manoj
Deepack
Mobashah
Which is the best solution plz confirm the solution A or B!
I'm with Manoj couz he is administering and work in the 15 TB database size and it is not easy to work in this ambient, but still not 100 % confirming!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply