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 ?
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
If we reorganize same: (Hope you can reproduce same figures)
No comments:
Write commentsPlease do not enter spam links