Geek Answers Handbook

Backup db recovery from SQL Server 2008 r2 in 2008

I need to backup and restore db on sql server 2008R2 server on sql server 2008. The backup file is incompatible in this way. Another option I found is to generate scripts containing data. Will the insertions succeed since I have db normalized with a lot of foreign keys?

Is there any other reliable approach?

+4
source share
4 answers

As you noted: direct recovery cannot . SQL Server has never been backward compatible in the sense of restoring a database from a newer version to a server with an old version.

What you can do is use the generated INSERT statements (using SSMS directly or a convenient add-in like the SSMS Toolpack ), or you can use a good SQL DIFF tool like Red-Gate SQL Data Compare to compare your two databases ( even from the backup file, in the pro version!) and update one from the other.

+5
source

SQL Server does not allow downgrades, you can back up your database in an instance of SQL Server 2008R2 and use the third-party tools ApexSQL Diff and ApexSQL Data Diff to restore a backup of an old version of SQL Server.

You can read a detailed explanation of the process in this article: Restoring a SQL Server Database Backup for an Old Version of SQL Server

Hope this helps

Disclaimer: I work for ApexSQL as a support engineer

+1
source

You can try using the data export wizard. Right-click on the database in SSMS, tasks, export data and copy all or any objects that you need.

0
source

Backward compatibility is not available in SQL Server 2008 R2. One solution is to have the database script create and start the required server.

0
source

More articles:


All Articles