Much has been written about the log backup chain in SQL Server, where we are able to restore a database to a point in time using a combination of full, differential and transaction log backups.
This requires a database being in Full Recovery Mode and transaction log backups being taken at regular intervals.
If the database is put into simple recovery mode, and then switched back to full recovery mode, the log backup chain is broken, and a full or differential backup must be taken to fix it.
I’ve been working on a long-term project to migrate over fifty databases running on SQL Server 2008 R2 to Azure virtual machines running SQL Server 2014.
We use a custom form of Log Shipping to keep the Azure environment in sync with production. The log chain is stored on Azure Blob Storage, and each log file is restored every half hour to the new environment using a custom PowerShell script.
This allows us to have a very small maintenance window for the final cutover, requiring just the tail of the log backup to be restored.
Last week, I wondered aloud what would happen if we had to fail back to the original system, after processing transactions on the new environment. Would a log backup taken on the Azure environment work on the older servers? Technically speaking, the log backup chain would not have been broken. Perhaps a version conflict?
Let’s look at a test case.
- Create a database in SQL Server 2008 R2 with Full Recovery, and perform a full backup, plus several transaction log backups.
- Restore the full backup and log chain to a new SQL Server 2016 instance, perform some actions here, and then perform another log backup from the new instance.
- Test if the newest log backup can be applied, along with the older ones, in a recovery on SQL Server 2016. I say “Yes”.
- Test if the newest log backup can be applied, along with the older ones, in a recovery on SQL Server 2008 R2. I say “No”.
Tune in next week for the results. You’re always welcome to comment here or discuss it on Twitter at @bornsql.