Restoring SQL 2008 R2 db on SQL 2008 Standard

Question

I'm trying to take a SQL Server 2008 R2 Expression db and restore it on my local SQL Server 2008 standard, however it gives me the error:

The media family on device 'C:\jtoth\Backups\ucampus.bak' is incorrectly formed. SQL Server cannot process this media family.

R2 is version 660 and standard is 655 and I know that you can't restore to an older version, but there has to be some way around this. Is there some free tool for scripting out the entire R2 database (including not just structure but inserts for the data) that I can then run on my standard instance?

Latinist

To my knowledge, you can't restore a newer SQL Server version database to an older one. You could use a Linked Server instance to transfer the database between, assuming one time use. – Lisalisabet

Answer 1

Normally one of

• SSIS (via the import/export wizard)
• 3rd party tool from Red Gate or similar.

Saltine

Import/export doesn't give the option to script an entire database of its objects and data. Going to give Red Gate a shot... – Latinist

Answer 2

You're right that the backup cannot be restored. See: SQL Server 2008 R2 bumps the database version. Your best bet would probably be the Red-Gate compare tools.

Moon

Answer 3

Use the Copy Database Wizard!, the Copy Database Wizard lets you move or copy databases and their objects easily from one server to another.

Twine

Did not work for me. I got a message saying "The database '[databasename]' cannot be opened because it is version 661. This server supports version 655 and earlier. A downgrade path is not supported." – Hulda

Answer 4

In SQL Server Management Studio, right click on the database, choose Tasks, then "Generate Scripts". Options in there to generate full scripts including the table structure and all the data. Additionally you can choose which version to script for.

Outdate

Generate scripts seemed promising but I didn't have success with it. It generated a script but when running it multiple database tables weren't created and most of the data was not inserted, even though no errors were thrown. It seems like it must be buggy, has anyone else had experiences with it? – Latinist

I've done this in the past without running into any problems; Have you looked at the generated scripts for the missing tables/data? Is there anything common about the missing tables, maybe a particular schema or permission is missing? – Outdate

No sorry I didn't bother because it was such a large script that it was hard to read and even SSMS couldn't handle it, as it chokes on large scripts. – Latinist

You could always script the data and the schema separately; Then at least the schema would be easy to check for missing tables and such. – Outdate

Answer 5

Visual Studio has a Database Compare tool. Might be handy. See the Data menu. Not as good as 3rd party tools though..

Shawannashawl

Answer 6

Just an idea - how about changing compatibility level to SQL Server 2005 before backup? That is of course if nothing 2008 specific was used.

Lingerfelt