Restore of Case Insensitive Database to a Case Sensitive Database - SQL Server 2000

Yesterday I received a response to my CI/CS Collation problem and the
recommendation was to try and restore a CI Collation database to a CS
Collation database. After creating a blank CS database a full restore
(Force restore over existing database) does change the Collation to
CI. I'm unsure as to how I can restore without changing the
Collation. Any suggestions?Hi. Check out this article:

http://www.sql-server-performance.c...mparison_sp.asp

If appears that if you can put the CI db on another box and create a
linked server to it you should be able to bring over everything to the
CS db and avoid all your collation issues by setting one flag.

I've recently been wrestling with collation issues too due to
developing with SQL2K PersEd and then trying to migrate to SQL2K for
production. What a nightmare. In my case, I want to use the
production server's default collation and I saw an article about
restoring one collation over another (it sounds like that's what you
tried) but it didn't work for me either. I resorted to renaming the
old db and scripting all the objects in it, removing any DDL
references to collation settings (so that running the scripts would
use the new db's default), creating a new database with the original
db name, and then running the scripts to create the new objects. Then
via DTS I ran queries to bring all the data over. My databases are
fairly small and this was still a lot of work but it succeeded. It may
not be a viable option for you though.

Good luck, and if you find an easier way of doing this please let me
know.

JT

swansons@.optimalinternet.com (Sue Swanson) wrote in message news:<c93054c8.0307010414.44475a5@.posting.google.com>...
> Yesterday I received a response to my CI/CS Collation problem and the
> recommendation was to try and restore a CI Collation database to a CS
> Collation database. After creating a blank CS database a full restore
> (Force restore over existing database) does change the Collation to
> CI. I'm unsure as to how I can restore without changing the
> Collation. Any suggestions?|||Sue Swanson (swansons@.optimalinternet.com) writes:
> Yesterday I received a response to my CI/CS Collation problem and the
> recommendation was to try and restore a CI Collation database to a CS
> Collation database. After creating a blank CS database a full restore
> (Force restore over existing database) does change the Collation to
> CI. I'm unsure as to how I can restore without changing the
> Collation. Any suggestions?

This does not seem like a workable way to me. It is possible
that the default for new tables in the new database would be CS, but
the existing tables in the backup would retain their CI collation.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp