SQL Server – Restoring a Database with Compression on Non-Enterprise Edition

compressionrestoresql serversql-server-2012

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:

Decompress the object from the backup without Enterprise Edition using some sort of utility.
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?

Best Answer

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.

Related Solutions

Sql-server – SQL Server 2008 Restore

Try this:

CREATE DATABASE foo;
GO
CREATE DATABASE bar;
GO
BACKUP DATABASE foo TO DISK = 'c:\temp\x.bak';
BACKUP DATABASE bar TO DISK = 'c:\temp\x.bak';
GO
RESTORE HEADERONLY FROM DISK = 'c:\temp\x.bak';

Relevant columns for this scenario:

Position  DatabaseName
--------  ------------
1         foo
2         bar

To restore foo, whether it exists or not:

RESTORE DATABASE foo FROM DISK = 'c:\temp\x.bak'
  WITH FILE = 1, REPLACE;

To restore bar, whether it exists or not:

RESTORE DATABASE bar FROM DISK = 'c:\temp\x.bak'
  WITH FILE = 2, REPLACE;

If you don't specify WITH FILE, you will get a slew of errors, depending on whether the database in Position = 1 already exists.

The lessons here:

do not use common and generic names for backup files - name them for the database they represent and keep them separate.
use WITH INIT if you expect a backup file to only ever contain one copy of a backup.
understand what is in your backup by first running RESTORE HEADERONLY and/or RESTORE FILELISTONLY.

If you show us those two columns from the output of RESTORE HEADERONLY and what database you are actually trying to restore, we can provide you with a specific command that should work.

Sql-server – Error 3203 restoring SQL Server backup: The data is invalid

Try running a RESTORE FILELISTONLY or RESTORE HEADERONLY. If these run successfully, that would prove that SQL Server is successfully accessing the .bak file. (And rule out some sort of permission issue.)

If that is successful, try running your RESTORE VERIFYONLY and RESTORE DATABASE with STATS=1. If it continues to fail in the same spot each time, it would probably indicate that your backup is corrupt, and you're out of luck.

If it fails in a different spot each time, it could be an issue with reliability of the location where your .bak is saved.

Best Answer

Related Solutions

Sql-server – SQL Server 2008 Restore

Sql-server – Error 3203 restoring SQL Server backup: The data is invalid

Related Question