Hi There i have two windows 2000 servers which are both running SQL and i would like to restore a backup from one server to the other. Which in my opinion should be an easy task but when i go into the restore option and point it at the file i would like to restore i get the follwoing error
"The database you are attempting to restore was backed up under a different sort order ID (52) than the one you are currently using on this server (50) and at least one of them is a non binary sort order. Backup or restore operation operation terminating abnormally."
The server that i am trying to restore to already has databases on this so i cannot just reinstall SQL and change the sort order not that id know how to do that but this is what i have read.
Is htere anyway that i can put insome script for the database to fix this ???
The problem is that the databases have different collations, possibly due to database specific settings. The way to check this is to use a code snippet like:
DECLARE @cDb sysname
SET @cDb = Db_Name()
SELECT CAST(DatabasePropertyEx(@cDb, 'SQLSortOrder') AS TINYINT)
, DatabasePropertyEx(@cDb, 'Collation')
Execute it in both the source and the destination databases, and verify that the results are different.
The work around is to DROP the destination database before doing the restore. At that point you may want to consider changing the database collation, but this has many implications that you really need to think through before you make the change.
You were certainly close enough... Some collations share sort orders, which is why I wrote the query to show both of them.
I suspect since I gave the work around up front that we'll never hear from the OP again. I always hate it when that happens, since I like to know whether my proposed solution solved the problem or if they just gave up.
Based on the connection icon in your JPG file, the IRSNNS01PSGE server appears to be running SQL 6.5 or earlier (an old TDS connection). You can't install a backup from a later version of MS-SQL onto a server running an earlier version. This doesn't work even at the service pack level within a given release, much less on earlier server architectures like this appears to be.
You should be able to copy from 7.0 to 7.0 regardless of service pack. I do not recall any of the service packs making user database structure changes. SQL 2000 introduced the database level collation, if I recall correctly. Does this database require that particular sort order? For 7.0, I think the only option is to
1) Script all objects and permissions
2) Create new database on the destination server with that script
3) Transfer the data using either BCP or DTS.
With the change of sort order and maybe code page, you could run into a number of tricky problems, though.
I tend to agree with you on the Backup -restore it should not really matter which version of sql srv 7 i use they should still all do the same thing. Can you imagine an outlook version not working because it is outlook 98 sp1 sending it to outlook 98 sp2 ??
I am a step ahead of your solution in that ive tried all that before i even started this thread. I can genreate SQL and export but i do get errors and plenty of them when i run them on the new server mainly to do with the case sensitivity. I dont need all of the information from the other server so at the moment i just trying to bring across and manually fix the code for each stored procedure that i am using. Its not ideal but it should work. Id much rather have a procedure or alternative way to do this considering there is over 190 stored procedures and at the moment im nost sure which ones are being called from where.