Last week I wondered whether a log backup chain would survive a database migration. Unsurprisingly, the answer is “yes”.
I also asked if a log backup taken on the new instance would be usable on the old instance, if the need arose to fail back to the original environment. Unsurprisingly, the answer is “it depends”.
Setting up the Test
Using our test case from last week, we start with a database on a SQL Server 2008 R2 instance.
CREATE DATABASE [TransactionLogChain];
GO
Make sure the database is in Full Recovery Mode, and then make sure we have a full backup, otherwise the database remains in pseudo-simple mode until it is backed up.
-- Full Recovery Mode, Right Now!
ALTER DATABASE [TransactionLogChain]
SET RECOVERY FULL WITH NO_WAIT;
GO
-- Full database backup
BACKUP DATABASE [TransactionLogChain]
TO DISK = N'D:\SQLBackup\TLC.bak';
GO
We now create a table to write to, taking a transaction log backup afterwards.
-- Identity column only
CREATE TABLE [dbo].[TransactionLogChain]
([ID] INT IDENTITY(1,1) NOT NULL);
GO
Insert some rows to have a database worth having:
-- Use the GO <n> batch processor to run 50 times
INSERT INTO [dbo].[TransactionLogChain] DEFAULT VALUES;
GO 50
Take a transaction log backup of this SQL Server 2008 R2 database.
-- Transaction log backup containing table creation
-- and first 50 row inserts
BACKUP LOG [TransactionLogChain]
TO DISK = N'D:\SQLBackup\TLC_01.trn';
GO
That’s enough to begin testing our first scenario because we have to restore this database to our SQL Server 2016 instance. Remember to use NORECOVERY because we have to apply the log afterwards.
-- Full backup is restored first
USE [master];
GO
RESTORE DATABASE [TransactionLogChain]
FROM DISK = N'D:\SQLBackup\TLC.bak'
WITH FILE = 1,
MOVE N'TransactionLogChain' TO N'D:\SQL2016\TLC.mdf',
MOVE N'TransactionLogChain_log' TO N'D:\SQL2016\TLC_log.ldf',
WITH NORECOVERY;
GO
Apply the log backup. A good habit is to restore all log files with the NORECOVERY option as well, just in case. When we are sure we want to bring the database online, then we can simply issue a WITH RECOVERY command.
-- Log backup is restored next
USE [master];
GO
RESTORE LOG [TransactionLogChain]
FROM DISK = N'D:\SQLBackup\TLC_01.trn'
WITH NORECOVERY;
GO
Bring the database online:
RESTORE DATABASE [TransactionLogChain] WITH RECOVERY;
GO
We now have an online version of our database on the SQL Server 2016 instance.
Let’s run the insert script again to add another 50 rows to the table.
-- Use the GO <n> batch processor to add another 50 rows
INSERT INTO [dbo].[TransactionLogChain] DEFAULT VALUES;
GO 50
A quick SELECT tells us that the table has 100 row(s) affected.
Test 1: Using a log from a new instance
Theoretically, nothing is wrong with this process so far. SQL Server considers the log backup chain unbroken. We can take a log backup on the new instance and use that in connection with the existing log backup chain to restore our database to its current state on SQL Server 2016.
-- Transaction log backup containing extra 50 rows
BACKUP LOG [TransactionLogChain]
TO DISK = N'D:\SQLBackup\TLC_02.trn';
GO
Let’s test this. First we must drop the [TransactionLogChain] database from the 2016 instance.
USE [master];
GO
DROP DATABASE [TransactionLogChain];
GO
Restore all three files now: the full backup and transaction log backup from 2008 R2, and the 2016 transaction log. Finally, bring the database online.
USE [master];
GO
RESTORE DATABASE [TransactionLogChain]
FROM DISK = N'D:\SQLBackup\TLC.bak'
WITH FILE = 1,
MOVE N'TransactionLogChain' TO N'D:\SQL2016\TLC.mdf',
MOVE N'TransactionLogChain_log' TO N'D:\SQL2016\TLC_log.ldf',
WITH NORECOVERY;
GO
RESTORE LOG [TransactionLogChain]
FROM DISK = N'D:\SQLBackup\TLC_01.trn'
WITH NORECOVERY;
GO
RESTORE LOG [TransactionLogChain]
FROM DISK = N'D:\SQLBackup\TLC_02.trn'
WITH NORECOVERY;
GO
RESTORE DATABASE [TransactionLogChain] WITH RECOVERY;
GO
USE [TransactionLogChain];
GO
SELECT * FROM [dbo].[TransactionLogChain];
GO
And our result is the same:
(100 row(s) affected).
Second Test: Applying a new log to an old instance
Now let’s take a look at applying a log backup chain to the original instance.
The full backup and first log file restore just fine, but on the log backup taken from 2016, we get the following error:
Msg 3241, Level 16, State 0, Line 17
The media family on device 'D:\SQLBackup\TLC_02.trn' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 17
RESTORE LOG is terminating abnormally.
As I originally suspected, the version of SQL Server on which the newer log backup is taken matters.
I was able to take a SQL Server 2008 R2 backup, restore it to SQL Server 2016, run a log backup, and restore the entire chain on SQL Server 2016.
I was not able to restore the 2016 log to the 2008 R2, 2012 or a 2014 instance, however.
If for whatever reason we need to fail back to the original environment, using logs taken in the new environment, we must be running the same version of SQL Server.
The build does not seem to matter as much, but honestly, don’t take my word for it.
How does this help me?
Let’s say we are migrating a massive database and have used log shipping to keep the old and new environments in sync. As soon as we cut over to the new environment, we can continue taking log backups and be confident that the existing log shipping files and original full backup can be retained, until our next full backup.
Please feel free to leave a comment here, or find me on Twitter at @bornsql. I would love to hear your thoughts.