2

Is it possible to restore a compressed an object from backup without the Enterprise Edition?

I'm getting the following error after a RESTORE DATABASE operation.

Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Msg 909, Level 21, State 1, Line 2
Database 'abc' cannot be started in this edition of SQL Server because part or all of object 'def' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Msg 933, Level 21, State 1, Line 2
Database 'abc' cannot be started because some of the database functionality is not available in the current edition of SQL Server.

I can think of two solutions but I don't know whether it's possible or how to do it if so:

  1. Decompress the object from the backup without Enterprise Edition using some sort of utility.
  2. Remove the offending object from the backup using some sort of utility so I can use the other non-compressed data. (Not sure how it would handle references such as foreign-keys though.)
    • Would it be possible to somehow do this in SQL Server? It's refusing to load the entire database even though not everything is compressed. Is there a way to force SQL Server to attempt to load the other tables?
4
  • 2
    Or. Or. Restore a copy of the database where it exists (or onto a copy of Eval or Dev edition), remove compression, and take a new backup. Commented Apr 29, 2016 at 16:12
  • @AaronBertrand "Free"! Music to my bosses' ears :) Are there any limitation (such as db size, etc.) on those editions?
    – user193130
    Commented Apr 29, 2016 at 16:38
  • 3
    Well, Developer Edition for 2012 is $49 or $59 depending on where you find it (if you still can). Only 2014 is now free. If you have to restore onto 2012 Standard Edition, you can't restore to 2014 first and then restore to 2012 - you can go up but you can't go down, so be careful there. (How do you have a backup of a database that is on Enterprise Edition but you don't have access to the instance it came from?) Commented Apr 29, 2016 at 16:41
  • @AaronBertrand Thanks, that's really helpful to know -- I wouldn't have thought that I can't just use the 2014 Dev Edition to export it for import again in the 2012 Standard Edition. Regarding why I don't have access to the original Enterprise Edition instance, it's because the backup came from a different department. We are importing their data for our use through their backup files. Unfortunately, we probably won't be able to convince them to simply rebuild their database without compression just because our wallets are not as fat.
    – user193130
    Commented Apr 30, 2016 at 4:25

1 Answer 1

3

No !

But, you can :

1 script our your database schema and use BCP out and bcp in method or

2 restore the database as a copy on EE or Dev edition, remove all enterprise features and backup the database and restore it on standard edition.

5
  • It won't allow me to generate script for the "Suspect" database; do you mean to do the BCP out on the original Enterprise Edition server? Unfortunately, we don't have Enterprise Edition as the backup came from another department, and it's unlikely they would rebuild the database for us, especially since it's fairly large.
    – user193130
    Commented Apr 29, 2016 at 16:35
  • How is the database Suspect ? You have not mentioned anything in the question ! Check this (sqlskills.com/blogs/paul/…) blog post to first recover from suspect database. You can use Dev edition to restore the database (2012 is not free but under $50).
    – Kin Shah
    Commented Apr 29, 2016 at 16:42
  • It automatically entered the Suspect mode when the RESTORE DATABASE failed with the messages above. Was it supposed to enter a different mode rather than the Suspect mode when this error occurred? It's unlikely the backup is also corrupted on top of the compression issue though.
    – user193130
    Commented Apr 30, 2016 at 5:10
  • @user193130 Yes, you'd have to generate the BCP or other scripted data from the original source or a successful restore of your backup. That won't happen on Standard Edition. Just drop the suspect database that resulted from your attempt. Commented Apr 30, 2016 at 11:08
  • Just realized I forgot to follow up on this -- we couldn't find a solution apart from what you stated so we ended up just purchasing the enterprise edition and it worked flawlessly. Thanks for your insight
    – user193130
    Commented Mar 13, 2017 at 21:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.