Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Feb 2008
    Posts
    10

    Unanswered: Different Sort Orders ??? please help

    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 ???

    Im using Enterprise manager with SQL server 7

    Thanks in advance

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The databases have to be of the same collation for you to be able to restore to the instance.

    In EM right click the database, go to properties and check the collation names used on both instances.
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2008
    Posts
    10

    Collation settings

    the settings all seem the same to me for the collation settings is it the code page that you are looking for because these are both set to cp 1252

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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:
    Code:
    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.

    -PatP

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So I was right..?
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

    -PatP

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by georgev
    So I was right..?
    Quote Originally Posted by Pat Phelan
    You were certainly <snip>



    I think that's the first "DBA Style" question I've answered on these forums correctly
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2008
    Posts
    10

    Red face Well since you really wanted to know

    Well since you really wanted to know

    first of all

    'DatabasePropertyex' is not a recognized function name. so i changed it to DatabaseProperty and

    the results that i got back from both databases were the same as marked below

    ---- -----------
    NULL NULL

    (1 row(s) affected)

    any other suggestions as i dont intend giving up yet
    Attached Thumbnails Attached Thumbnails sql error.jpg  

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Can you run
    Code:
    select @@version
    on both, and post the result?

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

    -PatP

  11. #11
    Join Date
    Feb 2008
    Posts
    10

    select @@version

    The main system that the database is already running on gives me this response


    Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86)
    Apr 9 2002 14:18:16
    Copyright (c) 1988-2002 Microsoft Corporation
    Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)


    (1 row(s) affected)


    the system that i would like to copy the database to gives me this response


    Microsoft SQL Server 7.00 - 7.00.961 (Intel X86)
    Oct 24 2000 18:39:12
    Copyright (c) 1988-1998 Microsoft Corporation
    Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)


    (1 row(s) affected)

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Pat Phelan
    You can't install a backup from a later version of MS-SQL onto a server running an earlier version
    Quote Originally Posted by Jim O Sullivan
    The main system that the database is already running on gives me this response

    Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86)
    Apr 9 2002 14:18:16


    the system that i would like to copy the database to gives me this response

    Microsoft SQL Server 7.00 - 7.00.961 (Intel X86)
    Oct 24 2000 18:39:12
    Notice anything?
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2008
    Posts
    10

    Restoring the database

    Well if i Cant copy the database and i cant restore the database then can i just copy the mdf file and the ldf file and then put them on the new server and then connect to them ???

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  15. #15
    Join Date
    Feb 2008
    Posts
    10
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •