locked
Difference between creating and building a database and restoring a database RRS feed

  • Question

  • Hello all,

      I have a database with compatibility level 80 (sql server 2000). the server got upgraded to sql server 2008 R2 without changing the compatibility level ( which should be 100).

    Please help me understand what will be the difference between me generating the scripts for the database(for the database, schema and data) and creating a new copy of the database and RESTORING the database from an existing backup.

     

    Thanks in advance,

    Ananthram


    -Thanks, Ananthram
    Tuesday, January 24, 2012 9:16 AM

All replies

  • If you want the database to be upgraded from SQL Server 2000 to 2008 R2 as part of scaling up,then compatibility level should be changed to 100.

    The exact process for upgrade will be - 

    1. Run upgrade adviser for 2008 R2 on 2000 database and find out potential issues.This will point out issues which might come when you move forward.

    2.Once fixes are scripted out,you can backup and do a restore of the database.

    3.Change compatibility.

    4.Do a Update Stats.

     

     


    Anup | Database Consultant

    Blog: www.sqlsailor.com Twitter: Follow me !

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Peja Tao Thursday, January 26, 2012 1:53 AM
    Wednesday, January 25, 2012 9:49 PM
  • Anup,

     

    Thank you for your valuable tip above.

    In my case, the upgrade to 2008 R2 was done by some one onsite apparently without running the upgrade advisor. So I now, do not have the upgrade advisor hlep available at my disposal.

     Onsite people now want me to rebuild the database by generating the scripts from the existing database, change the compatibility to 100 and then populate data into the newly created database(which now has a compatibility of 100) using the script which I generate from the existing database.

    So I want to understand what will be the difference between me generating the scripts for the database(for the database, schema and data) and creating a new copy of the database with a compatibility of  100 and populating the data and altering the existing database to be compatible to 100

     

    Thanks in advance,

    Ananthram


    -Thanks, Ananthram
    Saturday, January 28, 2012 5:55 AM
  •  

    So I want to understand what will be the difference between me generating the scripts for the database(for the database, schema and data) and creating a new copy of the database with a compatibility of  100 and populating the data and altering the existing database to be compatible to 100

     

    Thanks in advance,

    Ananthram


    -Thanks, Ananthram

    Hi Ananthram,

    If you generated the scripts for the SQL Server 2000 database (with compatibility 80) and ran it in the SQL Server 2008 R2 server (with compatibility 100), you may find the compatibility problem like query syntax while executing the generating script and fix it in time.

    But when you perform the second way you mentioned, you may find the compatibility problem while you do system testing with application and database.


    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.
    Thursday, February 2, 2012 1:41 AM
  • could you check the below link.

    http://www.orafaq.com/wiki/Oracle_database_Backup_and_Recovery_FAQ

    Tuesday, May 1, 2012 10:07 AM