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?
user193130
  • 223
  • 3
  • 6
  • 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. – Aaron Bertrand Apr 29 '16 at 16:12
  • @AaronBertrand "Free"! Music to my bosses' ears :) Are there any limitation (such as db size, etc.) on those editions? – user193130 Apr 29 '16 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?) – Aaron Bertrand Apr 29 '16 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 Apr 30 '16 at 04:25

1 Answers1

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.

Community
  • 1
Kin Shah
  • 62,225
  • 6
  • 120
  • 236
  • 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 Apr 29 '16 at 16:35
  • How is the database Suspect ? You have not mentioned anything in the question ! Check this (http://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/) 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 Apr 29 '16 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 Apr 30 '16 at 05: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. – Aaron Bertrand Apr 30 '16 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 Mar 13 '17 at 21:44