Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Restoring backup from an enterprise edition to express edition

Posted on 2010-08-26
7
Medium Priority
?
453 Views
Last Modified: 2012-05-10
Is there really a known issue when restoring a backup file from an enterprise edition of SQL 2005 to express edition?

I'm getting this error:

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'E:\MyBackup.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I tried running this too:
RESTORE FILELISTONLY    
FROM DISK = 'E:\MyBackup.bak'

same error:
The media family on device 'E:\MyBackup.bakk' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.



My backup is fine and restorable (tried restoring to a SQL 2005 enterprise version on a different machine)

Any insights? Thanks!

0
Comment
Question by:Faiga Diegel
  • 3
  • 3
7 Comments
 
LVL 3

Assisted Solution

by:Emileneth
Emileneth earned 600 total points
ID: 33537989
The software does not detect a valid backup, you could mount that backup in an Enterprise edition and export data in a different way, like sql files, then inject them in your Express edition.. if this fails it will show wich operation is invalid on the reduced version
0
 
LVL 15

Author Comment

by:Faiga Diegel
ID: 33538260
It wouldn't work too. I tired all possible backup style.
0
 
LVL 3

Expert Comment

by:Emileneth
ID: 33538396
What happens when trying to inject the SQL?

If you see, something like "Error executing SQL at line XXX" or something its an indication that the database design is not compatible with the reduced function set of the Express edition, in that case you will need to de-desing the database and build up a conversion tool
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 58

Accepted Solution

by:
cyberkiwi earned 900 total points
ID: 33538426
Based on this
[My backup is fine and restorable (tried restoring to a SQL 2005 enterprise version on a different machine)]

I guess that it could be a service pack issue.
Any Sql Server should be able to restore a database backed up from the same level and service packs (2005-2005, 2008-2008) regardless of edition.

Can you check and compare service pack levels?
0
 
LVL 15

Author Comment

by:Faiga Diegel
ID: 33538428
it's the same error. what I did for testing was create a database with one simple table in enterprise edition, insert one row, backup the DB and restore it to an express edition. it really will not work....
0
 
LVL 3

Expert Comment

by:Emileneth
ID: 33538459
Cyberkiwi: i agree, that's why i suggested a plain text sql file transmission, the language should be compatible as the standard doesnt change from SP to SP, and should be backwards compatible if restoring into a 2008.

Faiga: in your last post you mentioned a DB backup-restore process, have you tested the sql method?
0
 
LVL 15

Author Closing Comment

by:Faiga Diegel
ID: 33545902
My bad, the other server was a SQL 2008 R2. ANd i'm trying to restore it to a lower version (SQL 2005).

Thanks cyberkiwi and emileneth!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question