January 7, 2009 at 6:15 am
I need to change the Collation at server level, and am weighing up two options. 1) Server rebuild 2) using a command line to rebuild the master db with the correct db (as per msdn / bol etc.)
Solution 2 as I've seen it documented talks about scripting all databases, tables, other objects, and even data, and then regenerating everything with the scripts. A rebuild looks a lot simpler!
However my case is slightly different. I DO NOT want to change the collation of the databases i.e. I want them to retain their existing collation. Hence I don't think I need to bother with any of the db recreation stuff.
Has anyone any experience of this kind of thing?
Thanks,
David McKinney.
January 7, 2009 at 8:44 am
Hey David,
It looks like what you will need to do is follow option 1. There doesnt seem much point in changing the server collation and not rebuilding the sysdatabases.
Is it fair to assume that your databases are pre-2005 and collation specific?
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 7, 2009 at 9:53 am
Hi Adam,
I'm not sure I've been clear enough, about what my 2 options are (or else I've misread your response.)
OPTION 1
Rebuild server and start again (with the new collation.)
OPTION 2
Use the command line to modify the collation of the server.
e.g. setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=MSSQLSERVER /SAPWD="sa-pwd" /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /SqlCollation=SQL_Latin1_General_CP1_CI_AI
(this comes from MSDN http://msdn.microsoft.com/en-us/library/ms179254.aspx )
But the other steps in the MSDN article involve scripting out the entire databases - data and all!
-----------------------------------------------------------------
I suppose that the reason for all that scripting is to recreate the objects with the new collation and then insert the data correctly according to that collation. In my case however I don't want to upgrade the database collation - only the server collation. Hence the scripting wouldn't help me - au contraire!
Does this make more sense?
Thanks!
January 7, 2009 at 9:58 am
Briefly, as for my reasons for doing this:
I have a live server with binary collation (case sensitive) and a database with a Case insensitive collation.
On the Dev server, both the server and database collations are Case Insensitive.
I want to make the changes to the Dev server, so that it mirrors live, and so that problems we may have esp. regarding temp tables will show up in Dev and not when we go to do the live promotion!
(for what it's worth, the servers are 2005, but the DB is running under 2000 compatibility mode.)
January 7, 2009 at 10:00 am
Hii David,
I think we both misunderstood and some point. What you can do is run a SQLCLI command to rebuild the system databases. This is generally used to recover from corrupt system databases but can be used to rebuild into a new collation (i assumed you meant that).
Look at this doc link and let me know if you have any questions (i believe the subject has been covered pretty well on SSC as well).
Good Luck 🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave
January 7, 2009 at 10:08 am
Thanks for the tips Adam,
I expect that the command line I quoted is doing much the same "setup.exe /q /ACTION=RebuildDatabase ...." i.e rebuilding the system databases(?).
In any case the conclusion that I think we're both agreed on is that I don't have to spend a week scripting out my databases (and another week executing the scripts.)
Regards,
David.
January 7, 2009 at 10:15 am
Agreed.
I'm just looked at the article and those b*ggers have renamed it to 2008. Even Google says its 2005. Oh well, I guess it jsut works the same way in 2008.
You can also try: http://geekswithblogs.net/mskoolaid/archive/2005/12/17/63413.aspx
Let me know how it goes.
A.
Adam Zacks-------------------------------------------Be Nice, Or Leave
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply