How to install a specific backward compatible collation on a newer version of SQL
I need to install SQL Server 2008R2 onto a new server but with a collation / sort order that originates from a SQL Server 7 system. I find that all the relevant collation options available for selection in the install wizard are not a match for the database compatibility needed. Is it possible to install SQL Server in some other way and specify a very specific backward compatible collation ? What I need to install is expressed as collation: "Compatibility_50_409_0" It's a BINARY sort order - which in SQL Server 7 was Sort order 50, BIN_ISO_1 Binary from ISO 8859-1 (Unicode locale 1033, Case Sensitive) Any assistance would be greatly appreciated.
What happens if you restore that datbase from SQL Server 7.0 to 2000, then back that up and restore to SQL 2005, then back that up to SQL 2008R2? Give it a go, see what the collations are as you migrate upwards.
Thomas, if I upgrade SQL Server 7 in situ to SQL 2000 and then upgrade again etc all the way to SQL 2008 R2 it maintains the same sort order all the way. If I install SQL 2000 on the new fileserver and restore the SQL 7 databases into it, I can't get those application databases to match the master and other system databases in terms of collation - as I can't get the SQL 2000 install to that same SQL 7 collation in the first place. None of the Binary collation options in the install wizard is the right one.