Post reply

Collation Issue with Restore on different SERVER

  • December 22, 2015 at 5:35 am

    #399496

    Hi Gents,

    I have just restored a database from another Server but after restoring realised that the restored Database is using different Collation than what we have on all our other Databases. Would be great if someone can share experience how we can tackle this issue in the long run.

    Thanks much

    Shazme

  • December 22, 2015 at 5:50 am

    #1847405

    Shazme

    It's not always a problem if the database collation doesn't match that of the server. You usually get issues if your code creates temp tables and joins to those. If you do find it's a problem there's a script out there somewhere that will change the collation throughout your database. It's complicated because you have to drop some indexes, constraints and so on before you can do the changes - and them recreate them all afterwards in the correct order. Search for the script and see whether it works for you. Better still, restore on a server that has the same collation!

    John

  • December 22, 2015 at 6:29 am

    #1847418

    Hi John,

    Thanks for your reply. Will look for the script but regard you advice "Better still, restore on a server that has the same collation!"

    It's a DB which is restored from an operational System and in terabytes of data. In case I have not explained myself we have 6 other existing DB's with one collation and those will join to this new DB which is in different collation. This new DB contains TB's of data- changing through script might be an option but again don't want to change /break datasets while doing this.

    Thanks much

    Shazme

  • December 22, 2015 at 6:45 am

    #1847425

    If you are querying it from other databases with a different collation or vise versa you may need to specify a collation in the join or predicates to resolve any conflicts

    e.g.

    select Column1

    from table_1 t1

    join table_2 t2

    on t1.column collate SQL_Latin1_General_CP1_CI_AS = t2.column

  • December 22, 2015 at 10:06 am

    #1847503

    Just to be explicit- we will be getting a backup copy every now and again so want this to be done on DB Level. Totally understand we can do in query but that's what we are trying to avoid

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply