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.
-
1Note 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 BitsJan 6, 2018 at 17:19
Add a comment
|
2 Answers
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.
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.
answered Aug 30, 2016 at 15:52
-
2The 2 version rule isn't always true. I believe you can get from 2005 straight to 2016, maybe even 2017– GregJan 3, 2018 at 22:28
-
I have definitely restored backups three, four, and even five versions up. Jan 10, 2018 at 3:15