8

I have SQL Server 2012 backup files and want to restore these to SQL Server 2016/2014. Will it work?

I need to find this out before I uninstall SQL Server 2012.

  • 1
    Note that you cannot go the other way. If you do a backup on SQL Server 2016/2014, you cannot restore it on SQL Server 2012 even if the database you backup has a compatibility level of 2012. – Bacon Bits Jan 6 '18 at 17:19
11

Yes this will work. You can always go forward, you can't go backwards, at least not with backup and restore. With older version eg SQL 2000, SQL 2005 sometimes you have to do a stepped upgrade, ie go from 2000 to 2008 to 2014, but that shouldn't be a problem for you.

Be aware when you restore your databases they will have the older database compatibility level.

|improve this answer|||||
5

The short answer is yes. You can restore your database backup on a different version of SQL Server, but there is a tiny restriction. SQL Server allows you to restore only upwards, for example, from SQL Serve 2000 to 2005 or from 2008 to 2012. Please note, there is no way of restoring a database backup from SQL Server 2008 to 2005. SQL Server works in such way because every new version of SQL Server has modifications in the binary of the database and their storage. Also, there is no way to jump forward two versions, for example, you cannot restore a database from SQL Server version 2000 to SQL Server version 2012.

I have written the blog post "Hot Questions Related to SQL Server Backup" where you can find more information about this case.

|improve this answer|||||
  • 2
    The 2 version rule isn't always true. I believe you can get from 2005 straight to 2016, maybe even 2017 – Greg Jan 3 '18 at 22:28
  • I have definitely restored backups three, four, and even five versions up. – Anti-weakpasswords Jan 10 '18 at 3:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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