Can you restore a backup from SQL Server 2008 to 2005?

phowlett

I assume you would be able to take a backup taken from SQL Server 2005 and restore that backup onto SQL Server 2008. However, is it possible with Redgate Backup to restore in the other direction - i.e. take a backup from SQL Server 2008 and restore it to a 2005 database? I know there are new features and structures in 2008 that are not backwards compatible, but if you had a database running on 2008 at compatibility level 90 for 2005 and were using none of the 2008 specific features would the backup be backwards compatible?

I ask in the context of a planned rolling upgrade to 2008 (form 2005) and we ideally would need to be sure we could restore backups from the 2008 instances to the 2005 servers if we had to.

Thanks for any help,
Paul

Eddie D

Thank you for your post into the forum.

Unfortunately SQL Server will prevent you from restoring a backup of a SQL 2008 database to a SQL 2005 database.

SQL Server itself performs the actual backup or restore operation. When using third-party backup solution like SQL Backup, SQL Server performs the back or restore using a virtual device.

For a backup task, SQL Server generates the backup data and informs SQL Backup where to find the data. SQL Backup will then compress and encrypt (if configured) before writing the compressed backup data to disk.

The restore the opposite occurs SQL Backup reads the backup file from disk, if the backup file is encrypted, that data is decrypted and uncompressed. SQL Backup informs SQL Server where to find the backup data for restore.

If you have a need to restore a SQL 2008 backup file to a SQL 2005 database, please take a look at our comparison tools SQL Compare and SQL Data Compare Professional Editions.

<http://www.red-gate.com/products/SQL_Compare/index.htm&gt;
<http://www.red-gate.com/products/SQL_Data_Compare/index.htm&gt;

You can compare the live SQL 2008 database with the live SQL 2005 database or compare the SQL 2008 backup file with a live SQL2005 database.

Using SQL Compare you can synchronize to database schema from the SQL 2008 database to the SQL 2005, providing that the schema is not using the new features available in SQL 2008.

Next use SQL Data Compare to synchronize the data from the SQL 2008 database to the SQL 2005.

I hope the above answers your question.

Many Thanks
Eddie