This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
Can SQL Server 2016 log files be applied on SQL Server 2012
Question
This is in relation to a rollback scenario from a 2012 -> 2016 upgrade.
The scenario is that the SQL Server 2012 database is restored onto 2016 and log files applied until it is up to date. Then production systems are cut over to 2016. Database is kept at compatibility level for 2012. Subsequently, incremental logs from the 2016 instance are kept until the need to roll back (let’s say 8 hours worth).
Can the logs now be applied to the SQL Server 2012 instance to bring that up to date?
asked 2017-08-15 by 孔夫子
Answer
As soon as you bring the database online on SQL Server 2016, the database will be upgraded to the 2016 format, and will no longer be compatible with SQL Server 2012. Those changes are fully logged operations, and will be contained in your transaction logs.
The first transaction log backup taken on SQL Server 2016 will contain the script upgrade steps to bring the database from 2012 (110) to 2016 (130). If you attempt to apply that first transaction log backup to your copy of the database on SQL Server 2012, it will fail.
There is no way to do backup/restore (including log backups) to a lower version.
If you need to roll back, you would need to “manually” sync data–using either custom scripts, or a tool like Red Gate SQL Data Compare.
Generally, my preference is to not release the system for “public” use until after doing basic testing. If problems arise during smoke tests, you can roll back without having to sync data back to the lower version. If problems arise later, after smoke tests have passed, and after users are allowed back into the system–you commit to rolling forward and fixing the problem, rather than attempting to roll back.
answered 2017-08-15 by Andy Mallon