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!

dunxd
  • 9,632
  • 22
  • 81
  • 118
link470
  • 75
  • 3
  • 7

2 Answers2

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.

squillman
  • 37,883
  • 12
  • 92
  • 146
  • 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. – HopelessN00b Aug 16 '12 at 23:23
  • @HopelessN00b http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx Edition specific features. You should notice that there are several. – squillman Aug 16 '12 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? – HopelessN00b Aug 16 '12 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 '12 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 '12 at 00:15
  • @squillman Nah, but that's good to know. If you can edit that into your answer, I'll reverse the downvote. Thanks! – HopelessN00b Aug 17 '12 at 00:20
  • Done. Also reworded the bit about resource limitations to not make it sound like you didn't mention that. – squillman Aug 17 '12 at 00:27
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.

HopelessN00b
  • 53,795
  • 33
  • 135
  • 209