Error restoring database

[Kavius]

I am attempting to restore a database at a development location from a backup made at the live site. I want to bring the development data into sync with the live data. However, when I attempt to restore the database from the data file:

restore database MyDatabase from disk='C:\temp\final.bak'

I get the error:

The database you are attempting to restore was backed up under a different sort order ID (52) than the one currently running on this server (54), and at least one of them is a non-binary sort order.

Any advice on how to get this database up and running would be wonderful. Even if I have to create a new database. I just want the new data and structure.

 

[bperry]

I see no one has had a chance to respond today to your post. I'm not responsible for backups where I am, but I'll take a shot at it to get you started.

When SQL Server is installed, it is set up with a 'Sort Order' or 'Collation'. This business controls exactly what happens when you say Order By (or Group By) in a query.
You can find out how a server was set up by using the sp_helpsort procedure.

You can't restore a backup from one server to another, where the collation order is different between the source server and destination server.

So to accomplish this, you likely have to change the destination machine to match the collation on the backup.

As i mentioned, I'm no expert in this area (or in any area!!), but I understand that changing the collation order is a fairly big deal: it more or less amounts to re-installing the server. (at least on SQL 7. Not sure about 2000)

So that's the direction you have to look. Sorry I couldn't help more, but perhaps another forum reader will addd to this.

rgrds, etc
bperry

 

[Kavius]

According to SQL Server Books Online: Changing the Character Set, Sort Order, or Unicode Collation:

The character set, sort order, and Unicode collation are fundamental to the structure of all Microsoft® SQL Server™ databases. In order to change one or more of these settings, you must rebuild the master and user databases.

Thanks for the help, bperry. Unfortunately, you are right. This will be a very painful experience.