locked
How to restore a database with SQL_Latin1_General_CP1_CI_AS on a sql server enginee with SQL_Latin1_General_CP850_CS_AS RRS feed

  • Question

  • while I try to restore a database with SQL_Latin1_General_CP1_CI_AS  on enginee with SQL_Latin1_General_CP850_CS_AS, confliction error raise. How can I impelement my goal.Can by detatch and attach the mdf file? If can, is there possibily that data inconsistency happen.

    Thursday, January 10, 2013 8:08 AM

Answers

  • restoring should not raise the problem. we can have any collation for user defined database,

    but inorder to change the collation of system database we have to rebuild the system databases.

    can u please share the details of the error  .which u are getting while restoring the database.

    you can check my blog for rebuiilding the system databases

    http://sqlservr.blog.com/2012/12/20/rebuild-system-databases/


    Ramesh Babu Vavilla MCTS,MSBI


    Thursday, January 10, 2013 9:31 AM

All replies

  • restoring should not raise the problem. we can have any collation for user defined database,

    but inorder to change the collation of system database we have to rebuild the system databases.

    can u please share the details of the error  .which u are getting while restoring the database.

    you can check my blog for rebuiilding the system databases

    http://sqlservr.blog.com/2012/12/20/rebuild-system-databases/


    Ramesh Babu Vavilla MCTS,MSBI


    Thursday, January 10, 2013 9:31 AM
  • It should not be a issue.

    Please share the error message you are getting.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Thursday, January 10, 2013 3:08 PM
  • Hello Archer,

    Excerpt from http://support.microsoft.com/kb/325335?wa=wsignin1.0

    The following methods do not change a database's collation:
    • Backup and restore: Restoring a database on a server that has a different collation than the server that is used for the backup does not convert the restored database to the new collation. The database collation remains as is.
    • Detach and reattach: If you detach a database that was created with one collation and you reattach the database to another server that has a different collation, the collation of the database does not change. The collation of the database remains as is.

    I suggest you rebuild/reinstall your sql server with the correct collation.

    Thong

    Friday, January 11, 2013 1:28 AM
  • Migrating data between different collation is always a critical part.When you restore it in different collation, you should not get any error.

    One important thing is existing data will be in older collation, only new upcoming data will be affected by new collation. So better create tables in new collation server and move the data manually. This is what we did in our recent project.

    Please post the error meg.


    Srinivasan

    Friday, January 11, 2013 4:33 AM
  • Hi Archer,

    I want to inform that the database can be restored even if the instance on which you run RESTORE has a default collation different from the instance on which BACKUP was run. Please paste more details about the error message.

    Collation in BACKUP and RESTORE Operations
    http://msdn.microsoft.com/en-us/library/ms190725(v=sql.105).aspx


    Allen Li
    TechNet Community Support

    Friday, January 11, 2013 7:31 AM