Does Changes Due To Rebuilding \ Reorganize Index Is Saved in Differential or Log backup

While Playing with Database Restore strategy , A question comes to my mind

Q. Does Differential \ Log Backup captures movement of pages during Index Rebuild \ Reorganize ?

And Answer is YES.To justify same,I ran couple of test and found "if we take differential or log backup after rebuilding or reorganizing indexes then changes due to this maintenance are copied in backup" and you can restore same. Here I have tried to implement prove same/

                           DEMO:

/**** Create New Database for Testing ****/

CREATE DATABASE BKP_Test

GO

USE BKP_Test

GO

CREATE TABLE Diff_Table

(ID INT,

FirstName VARCHAR(100),

LastName VARCHAR(100),

City VARCHAR(100))

GO

/**** Creating Index ****/

CREATE  CLUSTERED INDEX PK_Diff_Table

ON Diff_Table(ID)

GO

/**** Run below insert twice ****/

INSERT INTO Diff_Table (ID,FirstName,LastName,City)

SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,

'Saurabh',

CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'SINHA'

ELSE 'Sumit' END,

CASE

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%1000 = 1 THEN 'New Delhi'

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'Mumbai'

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'Benglaru'

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Kolkata'

ELSE 'Bhopal' END

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

/**********************************************/

GO

/**** Check Fragmentation of Table ****/

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,

i.name AS IndexName,

indexstats.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Diff_Table'), NULL, NULL, 'LIMITED') indexstats

INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID

AND i.index_id = indexstats.index_id

GO

----> Take Full Backup

Backup database BKP_Test

to disk = 'C:\Backups\BKP_Test_Full1.bkp' with stats =1

GO

----> Rebuild Index

ALTER INDEX PK_Diff_Table ON Diff_Table

REBUILD;

GO

/**** Check Fragmentation of Table ****/

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,

i.name AS IndexName,

indexstats.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Diff_Table'), NULL, NULL, 'LIMITED') indexstats

INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID

AND i.index_id = indexstats.index_id

GO

----> Take Differential Backup

Backup database BKP_Test

to disk = 'C:\Backups\BKP_Test_Diff1.bkp' with stats =1 , Differential

GO

----> Take Log Backup

Backup log BKP_Test

to disk = 'C:\Backups\BKP_Test_log1.bkp' with stats =1

GO

----> Take Tail log backup

Backup log BKP_Test

to disk = 'C:\Backups\BKP_Test_log2.bkp' with stats =1 , norecovery

GO

Use Master

GO

----> Restore Full Backup

Restore database BKP_Test

from disk =  'C:\Backups\BKP_Test_Full1.bkp' with stats =1 , recovery

GO

use BKP_Test

GO

----> Checking Fragmetation after Full backup restore

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,

i.name AS IndexName,

indexstats.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Diff_Table'), NULL, NULL, 'LIMITED') indexstats

INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID

AND i.index_id = indexstats.index_id

GO

--:::: With full backup we get same fragmentation ::::

Use Master

GO

----> Backup tail log backup

Backup log BKP_Test

to disk = 'C:\Backups\BKP_Test_log3.bkp' with stats =1 , norecovery

GO

----> Restore Full backup

Restore database BKP_Test

from disk =  'C:\Backups\BKP_Test_Full1.bkp' with stats =1 , Norecovery

GO

----> Restore Differential Backup

Restore database BKP_Test

from disk =  'C:\Backups\BKP_Test_Diff1.bkp' with stats =1 , recovery

GO

use BKP_Test

GO

----> Check Fragmentation if it changes with Differential restore

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,

i.name AS IndexName,

indexstats.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Diff_Table'), NULL, NULL, 'LIMITED') indexstats

INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID

AND i.index_id = indexstats.index_id

GO

--::::With differential backup restore ,all changes with rebuilding index are coming ::::

Use Master

GO

----> Take Tail log backup

Backup log BKP_Test

to disk = 'C:\Backups\BKP_Test_log4.bkp' with stats =1 , norecovery

GO

----> Restore Full backup

Restore database BKP_Test

from disk =  'C:\Backups\BKP_Test_Full1.bkp' with stats =1 , Norecovery

GO

----> Restore Log backup

Restore log BKP_Test

from disk = 'C:\Backups\BKP_Test_log1.bkp' with stats =1 , Recovery

GO

use BKP_Test

GO

----> Check if fragmentation is changed

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,

i.name AS IndexName,

indexstats.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Diff_Table'), NULL, NULL, 'LIMITED') indexstats

INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID

AND i.index_id = indexstats.index_id

GO

--:::::With Log backup restore ,all changes with rebuilding index are coming::::

use Master

GO

/**** Clearing Environment ****/

Drop Database BKP_Test

I tried to test for reorganize also and its also same as rebuild. I am not able to post result due to large line of codes 

If we reorganize same: (Hope you can reproduce same figures)