What are the challenges you face when working across database platforms? Take the survey
Options

Interoperability

DanMoranDanMoran Posts: 14
edited May 6, 2011 2:07AM in SQL Backup Previous Versions
Can I backup a Compatability Level 9 DB from a 2008 SQL Server, using SQL Backup, and restore it as is to a SQL Server 2005 box?

Comments

  • Options
    peteypetey Posts: 2,358 New member
    No. When uncompressed, the contents of a SQL Backup backup file is identical to that of a regular SQL Server backup file, so the same restrictions apply i.e. you cannot restore a backup taken on a newer version of SQL Server to an instance running an older version of SQL Server. Compatibility levels only determine how certain server options should be treated, and does not maintain backwards compatibility of the data file(s) structures.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Thanks. I suspected this was the case, but wanted to be sure.
  • Options
    GYeohAMALGYeohAMAL Posts: 6 Bronze 1
    I'm new to this software, so just to be clear,

    1/ With Compression ON, you can backup a DB on a newer version of SQL and restore it to an older version of SQL ?
    (Assuming you aren't using any new SQL features that aren't available in the older version).

    AND

    2/ With Compression OFF, you can't backup a DB on a newer version of SQL and restore it on to an older version of SQL.


    I'm currently using SQL Backup ver 6.4.0.56, and can't restore a compressed DB backup from SQL 2005 to 2000. This clearly conflicts with statement 1/, but I'm guessing I am missing or misunderstanding some information here.
  • Options
    peteypetey Posts: 2,358 New member
    When uncompressed, the contents of a SQL Backup backup file is identical to that of a regular SQL Server backup file...
    Sorry that statement was not as clear as it could be.

    When you back up a SQL Server database, the backup data is stored in, let's call it, format A. When SQL Backup compresses the data, it takes this same data, in format A, and compresses/encrypts it, to let's call it format B. When you restore the database, SQL Backup uncompresses/decrypts the data in format B, back to format A, and passes that back to SQL Server, which happily works with format A, because that's the only format it recognises.

    SQL Backup only ever passes the original format of the backup data to SQL Server, and it will be up to the user to ensure that the correct SQL Server version is used when restoring. Thus, you can never use SQL Backup to restore a database that was backed up on a newer version of a SQL Server instance.

    Hope that's a better explanation.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    GYeohAMALGYeohAMAL Posts: 6 Bronze 1
    Yes, thanks Peter.
Sign In or Register to comment.