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)



No comments:

Post a Comment