Solved

Restoring backup from an enterprise edition to express edition

Posted on 2010-08-26
7
433 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:faiga16
  • 3
  • 3
7 Comments
 
LVL 3

Assisted Solution

by:Emileneth
Emileneth earned 200 total points
Comment Utility
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:faiga16
Comment Utility
It wouldn't work too. I tired all possible backup style.
0
 
LVL 3

Expert Comment

by:Emileneth
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 58

Accepted Solution

by:
cyberkiwi earned 300 total points
Comment Utility
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:faiga16
Comment Utility
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
Comment Utility
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:faiga16
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Select dynamic rows as columns? 3 53
SQL - sum months, quarter, YTD 9 80
How to check data in sql table 11 37
Analysis of table use 7 26
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now