Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

This question has probably been asked a dozen times but I couldn't find them here or at SO.

Can I attach/restore a SQL Server 2008 R2 database to my local SQL 2008 R2 Express instance?

share|improve this question

2 Answers

up vote 8 down vote

Not if you used any persisted Enterprise features. If you used any of such there will be an entry in sys.dm_db_persisted_sku_features and when trying to open/attach/restore this database on any edition lower than Enterprise you will gen an exception and the database won't open. The list possible of persisted SKU features are:

Compression. Indicates that at least one table or index uses data compression or the vardecimal storage format. To enable a database to be moved to an edition of SQL Server other than Enterprise or Developer, use the ALTER TABLE or ALTER INDEX statement to remove data compression. To remove vardecimal storage format, use the sp_tableoption statement.

Partitioning. Indicates that the database contains partitioned tables, partitioned indexes, partition schemes, or partition functions. To enable a database to be moved to an edition of SQL Server other than Enterprise or Developer, it is insufficient to modify the table to be on a single partition. You must remove the partitioned table. If the table contains data, use SWITCH PARTITION to convert each partition into a nonpartitioned table. Then delete the partitioned table, the partition scheme, and the partition function.

TransparentDataEncryption. Indicates that a database is encrypted by using transparent data encryption. To remove transparent data encryption, use the ALTER DATABASE statement. For more information, see Transparent Data Encryption (TDE).

ChangeCapture. Indicates that a database has change data capture enabled. To remove change data capture, use the sys.sp_cdc_disable_db stored procedure.

In addition, for Express edition specifically, the database must be smaller than the maximum database size supported by Express Editions (10Gb). There could be complications if you used any of the service pack specific options like sp_db_increased_partitions but I doubt that's the case.

If your database is clean of all of the above then Express will open it w/o problems. If the source is a Standard Edition then for sure it won't have any of the persisted Enterprise features and the only issue is size.

You need to make sure the SQL Server is the same major release (SQL Server 2008R2, not SQL Server 2008). The patching level (service pack and cumulative updates applied) does not matter as the database format cannot change between minor releases, but it would be recommended to have the Express patched to the latest SP and have the latest CU applied.

share|improve this answer
up vote 2 down vote

Yes, if the destination version is the same or higher. You can see this by SELECT @@VERSION

You can't restore to a lower version which has been asked many times

share|improve this answer
Thanks. I didn't know if Microsoft considered Express a lower version. – jao May 28 at 8:34
@jao: It is a lower edition which determines features. Version is the build and some internals – gbn May 28 at 8:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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