5

I have a server that's currently running SQL Server 2005 Express, and I need to restore a database backup that a developer has modified using SQL Server 2008 R2 [I think the full version, but still waiting to hear back on whether it's a full edition or Express].

I know that I can't restore a database from SQL Server 2008 R2 to SQL Server 2005 Express, and I know that I can't even restore a database from SQL Server 2008 R2 to SQL Server 2008. BUT! Can I restore a database from SQL Server 2008 R2 to SQL Server 2008 R2 Express? The only difference here being that one is a full paid version, and the other is the express version. If so, I'll just update the SQL Server 2005 Express server to 2008 R2 Express.

Thanks in advance!

2 Answers 2

2

Yes.

It's the database version that matters and is what gets attached to the database, not the edition of MSSQL used. As long as the database version is the same, you can move it between the free (Express) and paid versions to your heart's content... not that it's a process I'd recommend for recreational purposes.

EDIT: Since it's been mentioned, the relevant database-limitation in SQL Express 2008 R2 is a size limit of 10GB. It also has some limitations on the services and resources available to MSSQL Server, but those won't prevent you from restoring the database to it, only (possibly) using it how you'd like.

Full Technet feature comparison for 2008 R2 versions linked here.

2
  • Perfect, I'll go ahead with the database engine upgrade to SQL Server 2008 R2 Express. Thanks!
    – link470
    Aug 16, 2012 at 23:02
  • This is not true. Database version is not all that matters.
    – squillman
    Aug 16, 2012 at 23:17
3

It depends. If your database includes features that are not supported by the edition you are restoring to the restore will fail. For example, enterprise edition features like data compression and transparent encryption. This is in addition to the physical resource limitations that exist in Express.

If your database falls within the supported features / limitations of Express you should be all set.

7
  • Care to give an example of database-specific features SQL Express lacks that the paid versions have? Only one I can come up with is database size. Everything else only impacts on what the server will do, and do not impact on the database itself. Aug 16, 2012 at 23:23
  • @HopelessN00b msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx Edition specific features. You should notice that there are several.
    – squillman
    Aug 16, 2012 at 23:28
  • Yeah, I posted that 4 minutes before you did. Since you noticed "several," which ones specifically would prevent a successful restore to the Express edition? Aug 16, 2012 at 23:35
  • Thanks for the additional information. I think I'll be fine either way, as I've just received the call back that the developer is also using SQL Server 2008 R2 Express. So I simply need to update my installation and I think I'm good to go!
    – link470
    Aug 16, 2012 at 23:40
  • @HopelessN00b Sorry, stepped away for dinner. It's not quite the same. If your database employs any of the enterprise features, for example, it'll fail. Things like data compression and transparent encryption. If this is what you were talking about, then I apologize. It is not clear to me that this is what you're referring to.
    – squillman
    Aug 17, 2012 at 0:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .