locked
what are the correct steps to change the Compatibility level of the Database from SQL server 2000(80) to SQL server 2000 (100). RRS feed

  • Question

  • I have a existing database installed on SQL server 2000, it has been upgraded to SQL server 2008 using SQL server 2008 installation steps. The after Database the sucessful upgrade displays the compaibility mode as SQL server 2000 (80). As per the explanation form the MSDN site SQL server retains the database compaitbility level of the previous version of database from where it has been upgraded to.

    My question :

    1) If i want to manually server upgrade this Database to compatibility level SQL server 2008 (1000). What are the correct steps to perfrom this operation using the SQL Server application ?

    2) Does chnaging the value in compatibility level dropdown from SQL server 2000(80) to SQL server 2008(100) work fine ?

    3) what are the background chnages once the database has been chnaged?

    Please provide the responses to above at the earliest.

     

    thanks in advance.

    Jazz120

     

     

     

     

     

    Tuesday, January 17, 2012 9:30 PM

Answers

  • Hi Jazz120,

    The compatibly level setting is used by SQL Server to determine how certain new features should be handled.  This was setup so you could migrate your databases to a later release of SQL Server without having to worry about the application breaking.  This setting can be changed forward and backwards if needed, so if you do change your compatibly level and find that there are problems you can set the value back again until you resolve all of the issues that you may be facing during the upgrade.
     
    In addition, there are certain features that only work if the database is set to the latest compatibly level, therefore to get all of the benefits of the version of SQL Server you are running you need to make sure you are using the latest compatibly level.

    You could use the below command to change compatibility level:

    ALTER DATABASE database_name
    SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

     

     


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Proposed as answer by Manish_Kaushik Thursday, January 19, 2012 5:28 PM
    • Marked as answer by Peja Tao Wednesday, January 25, 2012 1:26 AM
    Thursday, January 19, 2012 2:22 AM

All replies

  • first of all you must test all your applications working fine after upgrade the compatibility on test environment before attempt to do it prod
    http://uk.linkedin.com/in/ramjaddu
    Tuesday, January 17, 2012 9:36 PM
  • Hi Jazz120,

    The compatibly level setting is used by SQL Server to determine how certain new features should be handled.  This was setup so you could migrate your databases to a later release of SQL Server without having to worry about the application breaking.  This setting can be changed forward and backwards if needed, so if you do change your compatibly level and find that there are problems you can set the value back again until you resolve all of the issues that you may be facing during the upgrade.
     
    In addition, there are certain features that only work if the database is set to the latest compatibly level, therefore to get all of the benefits of the version of SQL Server you are running you need to make sure you are using the latest compatibly level.

    You could use the below command to change compatibility level:

    ALTER DATABASE database_name
    SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

     

     


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Proposed as answer by Manish_Kaushik Thursday, January 19, 2012 5:28 PM
    • Marked as answer by Peja Tao Wednesday, January 25, 2012 1:26 AM
    Thursday, January 19, 2012 2:22 AM
  • Hi, You ran the database Upgrade Advisor?, with this utility help You to correct any issue in your application, in order to select the compatibility level

     

    Regards,

     


    If the answer was helpful, please mark it as useful. Si la respuesta le ayudó, por favor márquela como útil.
    Thursday, January 19, 2012 5:25 PM