JaniceCLee.com SQL…ish

Is restoring a transaction log backup a logged operation?

I was reading on logging recently and I wondered, when I restore a transaction log backup, do the rows get "re-logged" to the current log? I didn't think they would be. At most, I thought, they'd be logged minimally. I doubted that the transactions would get logged again. I did a quick test.

1. First, I created a test database, set it to full recovery model, and created a full db backup.

USE master
go
--Create the test database
CREATE DATABASE TestDB;
GO

--Set the recovery model to full
ALTER DATABASE TestDB SET RECOVERY FULL;
GO

--Full database backup
BACKUP DATABASE TestDB TO DISK = 'c:BackupTestDB_Full.bak';
GO

2. Then, I checked how much space was used at this point.

DBCC SQLPERF(logspace)

And the result for TestDB was (not all columns included):

Log Size (MB)
.5390625

3. Then, I ran the following script to fill the transaction log:

--Fill the transaction log
USE TestDB;
go
create table myTable (myNo int, myDate datetime)

DECLARE @a INT

SET @a = 1

WHILE @a < 20000
    BEGIN
        INSERT  INTO myTable ( myNo, myDate )
        VALUES  (
                  @a,
                  GETDATE()
                )
        SET @a = @a + 1
	    End

4. I checked the log space again.

DBCC SQLPERF(logspace)

The result's:

Log Size (MB)
11.17969

5. Next, I performed a transaction log backup.

--Perform a transaction log backup
BACKUP LOG TestDB TO  DISK = N'C:BackupTestDB.trn';
GO

6. Then I restored the full database backup and transaction log backup to a new database called TestDB2.

RESTORE DATABASE [TestDB2] FROM  DISK = N'c:BackupTestDB_Full.bak' WITH NORECOVERY,
      MOVE 'testdb' TO
'C:testdb2.mdf',
      MOVE 'testdb_log'
TO 'C:testdb2_log.ldf'
GO
RESTORE LOG [TestDB2] FROM  DISK = N'C:BackupTestDB.trn' WITH  RECOVERY
GO

7. And then I checked the log space again...

DBCC SQLPERF(logspace)

And the result was:

Log Size (MB)
11.17969

The size of the log after restore and the size of the log when we executed the fill-up script are the same. So it would seem the transactions do get re-logged during a restore.

Conclusion: (update)
The answer is Yes. A transaction log restore comprises of two steps: data is copied to the transaction log then rolled forward.