Restoring SQL 2008 R2 db on SQL 2008 Standard

views:

answers:

Restoring SQL 2008 R2 db on SQL 2008 Standard

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?

+2 A:

Normally one of

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

gbn 2010-10-21 17:47:20

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

Justin 2010-10-24 22:02:52

+1 A:

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.

Joe Stefanelli 2010-10-21 17:49:10

+1 A:

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.

Chris Shaffer 2010-10-21 17:53:56

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?

Justin 2010-10-24 22:04:04

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?

Chris Shaffer 2010-10-25 01:36:55

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.

Justin 2010-10-29 17:33:47

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.

Chris Shaffer 2010-10-29 21:07:08

ansaurus

tags:

views:

answers:

Restoring SQL 2008 R2 db on SQL 2008 Standard

related questions