Interoperability

DanMoran · May 4, 2011 5:06PM

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?

petey · May 4, 2011 10:05PM

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.

DanMoran · May 5, 2011 2:00PM

Thanks. I suspected this was the case, but wanted to be sure.

GYeohAMAL · May 5, 2011 9:54PM

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.

petey · May 5, 2011 11:07PM

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.

GYeohAMAL · May 6, 2011 2:07AM

Yes, thanks Peter.

Interoperability

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk