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

Improve this question

3 Answers 3

Reset to default
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.
Improve this answer
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).

Improve this answer
5
  • 2
    Not if they're using any Enterprise features not supported in Express (like TDE).
    – Erik Reasonable Rates Darling
    Commented May 22, 2017 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
    Commented May 22, 2017 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
    Commented May 22, 2017 at 12:08
  • 2
    @BhavikaZimbar there are more details about here about this by Aaron Bertrand.
    – SqlWorldWide
    Commented May 22, 2017 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
    Commented May 22, 2017 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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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