Changing the default collation for an instance of SQL Server can be a complex operation and involves the following steps:
- Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.
- Export all your data using a tool such as the bcp Utility. For more information, see Importing and Exporting Bulk Data.
- Drop all the user databases.
- Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:
setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQLEXPRESS
/SQLSYSADMINACCOUNTS=Administrator /SAPWD=******** /SQLCOLLATION=Slovak_CI_AS
- Create all the databases and all the objects in them.
- Import all your data.
For more information, see Rebuilding System Databases.
Hint: I have tried all the suggested methods to overcome Microsoft SQL Server, Error: 5123. I am going from 2005 to 2008. I was finally able to attach my DBs when I signed into SQL via Windows Authentication rather than SQL Authentication. Once attached I am now back using SA.