Post reply

SQL 2008 backup to SQL 2000

  • June 3, 2009 at 6:58 am

    #222030

    Hi

    Im busy debating this... my opinion is that even if a database is left in 2000 compatibility mode (when its resotered to sql 2008 ) that a backup of that database from SQL2008 cannot then be resorted into SQL 2000 ,,,,

    Im being told that there is a requirement for this to be so !

    Any words of wisdom etc ....

    thanks simon

  • June 3, 2009 at 7:05 am

    #1003968

    I would agree with you. I dont think its possible to Restore a 2008 database to 2000

  • June 3, 2009 at 7:13 am

    #1003981

    you are right.

    compatibility mode has nothing to do with how the native mdf or backups are constructed...it only decides what syntax for TSQL will be permitted to hit the database...that is also why you can change the compatibility on the fly...it has nothing to do with the behind-the-scenes database structure.

    you can only restore a database from a previous version to a higher version...so 2008 can take any 7,2000 or 2005 database, and when it gets attached or restored, it converts it to the new 2008 structure. 2005 can handle any thing previous to itself, but not "future versions".

    the only way to get data from 2008 to 2000 is to use SSIS or scripts to connect to a real 2000 server/instance, then let it copy the schema and data. The import/export wizard can help, but you need to make sure it does all objects. for that 2000 instance, you could then do a backup so it can be restored on a real 2000 instance.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 3, 2009 at 7:19 am

    #1003988

    thanks both ...oh how that news is going to go down so well !!!

    ~simon

  • June 3, 2009 at 7:23 am

    #1003990

    Simon_L (6/3/2009)

    Im busy debating this... my opinion is that even if a database is left in 2000 compatibility mode (when its resotered to sql 2008 ) that a backup of that database from SQL2008 cannot then be resorted into SQL 2000 ,,,,

    The compatibility mode just affects how some pieces of T-SQL are interpreted and executed by the query processor. It does not affect the internal database version, nor the structure of the data file nor the structure of the system tables. Those three are what prevents a database from being downgraded.

    A database attached to a SQL 2008 instance is a SQL 2008 database, regardless of what the compat mode is set to.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 3, 2009 at 7:34 am

    #1004000

    thanks gail ...

    p.s is that the babylon rangers motto I see ?

  • June 3, 2009 at 7:46 am

    #1004012

    Simon_L (6/3/2009)

    p.s is that the babylon rangers motto I see ?

    Yup.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 3, 2009 at 8:09 am

    #1004041

    nice....prefered farscape myself but ivanan always has a special place in my heart !

Viewing 8 posts - 1 through 7 (of 7 total)

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