0

I wanted to know that, can I restore backup of SQL Server 2016 Standard edition database into SQL Server 2016 Express edition database.

Or vice-versa

Brent Ozar
  • 42,952
  • 47
  • 220
  • 375
Bhavika Zimbar
  • 103
  • 1
  • 3

3 Answers3

4

Sometimes:

  • When restoring into Express Edition, the database size can't be larger than the size supported by Express (which varies by version)
  • When restoring into Express or Standard Edition from Enterprise or Developer Edition, if your database uses Enterprise-level features, the recovery portion of restore won't succeed (the Enterprise features vary starting with 2016 SP1, which put more features in Standard)
  • When restoring into any server, some features need to be enabled on the database server itself. Examples include replication, full text search, CLR.
Brent Ozar
  • 42,952
  • 47
  • 220
  • 375
1

Yes you can. Right click on your databases in SSMS (Task -> Back up) and create a back-up of your database, move it to the other host and restore it by right clicking on the server name in SSMS and chosing Restore.

Note that some functionality does not work in an Express edition (e.g. SSIS and SQL Agent).

maartenh
  • 11
  • 2
  • 2
    Not if they're using any Enterprise features not supported in Express (like TDE). – Erik Darling May 22 '17 at 12:05
  • Thank you for the answer. but if any case, if I want to restore the MS SQL 2016 Experss Edition database to MS SQL 2016 Standard edition database then is it possible? – Bhavika Zimbar May 22 '17 at 12:05
  • Yes this should work. I say should because I haven't encountered any issues to date with restoring a db from express to a full blown sql server instance. – maartenh May 22 '17 at 12:08
  • 2
    @BhavikaZimbar there are more details about here about this by Aaron Bertrand. – SqlWorldWide May 22 '17 at 12:09
  • And remember there is a database size limitation on Express. Your Enterprise DB might be too big to restore in Express. – Henrico Bekker May 22 '17 at 12:21
0

I believe that is definitely doable, however, Express edition can only hold databases 10 GB or smaller, so make sure that when restoring from any licensed version to Express edition , the database is under that size threshold. I can't think of any gotchas restoring from a Express to a licensed edition.

Guillermo Garcia
  • 182
  • 1
  • 11