articles

Articles of SQLschool.gr Team

Smart backups in SQL Server 2017

Antonios Chatzipavlis

Friday 05 October 2018

Presentation Code

use master;
go

/* demo setup */


if exists (select * from msdb.sys.backup_devices where name='dev1')
begin
    EXEC sp_dropdevice 'dev1';
end
exec sp_addumpdevice 'disk','dev1','W:\MSSQL14.DB01.BACKUP\dev1.bak';
go

drop database if exists SmartBackupDemo

create database SmartBackupDemo
on primary
( 
    name='SmartBackupDemo_data0'
,   filename=N'D:\MSSQL14.DB01.DATA\SmartBackupDemo_data0.mdf'
,   size=20MB
,   filegrowth=8MB
)
log on
(
    name='SmartBackupDemo_log'
,   filename=N'E:\MSSQL14.DB01.LOG\SmartBackupDemo_log.ldf'
,   size=8MB
,   filegrowth=8MB
);
go


use SmartBackupDemo;
go

drop table if exists T
go
create table T
(
    col1 int identity not null primary key
,   col2 varchar(512)
,   col3 int
,   col4 datetime2(7) default (sysdatetime())
)
go

set nocount on;
go

insert into T (col2,col3)
values ( convert(varchar(512),newid()), cast (ceiling(rand()*123456789) as int) %199 );
go 500000


create index idx_T_col2 on T(col2) include(col3);
go

create index idx_T_col4 on T(col4) include(col3);
go

set nocount off;
go

/* sys.dm_db_file_space_usage */

select  database_id
    ,   file_id
    ,   total_page_count
    ,   allocated_extent_page_count
    ,   unallocated_extent_page_count
    ,   modified_extent_page_count
from sys.dm_db_file_space_usage;
go

select  sum(total_page_count) as total_pages
    ,   sum(allocated_extent_page_count) as modified_pages_allocated
    ,   sum(modified_extent_page_count) as modified_pages_total
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(total_page_count) as decimal(5,2)) as [%_change_total]
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(allocated_extent_page_count) as decimal(5,2)) as [%_change_allocated]
from sys.dm_db_file_space_usage;
go

/* 1st backup */

backup database SmartBackupDemo to dev1 with format

/* 1st step */

delete top(10000)
from T
where col1>200000;
go

select  sum(total_page_count) as total_pages
    ,   sum(allocated_extent_page_count) as modified_pages_allocated
    ,   sum(modified_extent_page_count) as modified_pages_total
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(total_page_count) as decimal(5,2)) as [%_change_total]
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(allocated_extent_page_count) as decimal(5,2)) as [%_change_allocated]
from sys.dm_db_file_space_usage;
go

/* diff not clear */
backup database SmartBackupDemo to dev1 with differential;
go

select  sum(total_page_count) as total_pages
    ,   sum(allocated_extent_page_count) as modified_pages_allocated
    ,   sum(modified_extent_page_count) as modified_pages_total
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(total_page_count) as decimal(5,2)) as [%_change_total]
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(allocated_extent_page_count) as decimal(5,2)) as [%_change_allocated]
from sys.dm_db_file_space_usage;
go

backup database SmartBackupDemo to dev1;
go

select  sum(total_page_count) as total_pages
    ,   sum(allocated_extent_page_count) as modified_pages_allocated
    ,   sum(modified_extent_page_count) as modified_pages_total
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(total_page_count) as decimal(5,2)) as [%_change_total]
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(allocated_extent_page_count) as decimal(5,2)) as [%_change_allocated]
from sys.dm_db_file_space_usage;
go

/* 2nd step */

update top(20000) T
set 
    col3+=10
,   col2='name:'+col2
where col1 > 300000;
go

select  sum(total_page_count) as total_pages
    ,   sum(allocated_extent_page_count) as modified_pages_allocated
    ,   sum(modified_extent_page_count) as modified_pages_total
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(total_page_count) as decimal(5,2)) as [%_change_total]
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(allocated_extent_page_count) as decimal(5,2)) as [%_change_allocated]
from sys.dm_db_file_space_usage;
go

backup database SmartBackupDemo to dev1;
go

select  sum(total_page_count) as total_pages
    ,   sum(allocated_extent_page_count) as modified_pages_allocated
    ,   sum(modified_extent_page_count) as modified_pages_total
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(total_page_count) as decimal(5,2)) as [%_change_total]
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(allocated_extent_page_count) as decimal(5,2)) as [%_change_allocated]
from sys.dm_db_file_space_usage;
go

/* 3rd step */

set nocount on;

insert into T (col2,col3)
values ( convert(varchar(512),newid()), cast (ceiling(rand()*123456789) as int) %199 );
go 100000

set nocount off;


select  sum(total_page_count) as total_pages
    ,   sum(allocated_extent_page_count) as modified_pages_allocated
    ,   sum(modified_extent_page_count) as modified_pages_total
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(total_page_count) as decimal(5,2)) as [%_change_total]
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(allocated_extent_page_count) as decimal(5,2)) as [%_change_allocated]
from sys.dm_db_file_space_usage;
go

backup database SmartBackupDemo to dev1;
go

select  sum(total_page_count) as total_pages
    ,   sum(allocated_extent_page_count) as modified_pages_allocated
    ,   sum(modified_extent_page_count) as modified_pages_total
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(total_page_count) as decimal(5,2)) as [%_change_total]
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(allocated_extent_page_count) as decimal(5,2)) as [%_change_allocated]
from sys.dm_db_file_space_usage;
go

/* Size */

set nocount on;

insert into T (col2,col3)
values ( convert(varchar(512),newid()), cast (ceiling(rand()*123456789) as int) %199 );
go 10000

set nocount off;

select  sum(total_page_count) as total_pages
    ,   sum(allocated_extent_page_count) as modified_pages_allocated
    ,   sum(modified_extent_page_count) as modified_pages_total
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(total_page_count) as decimal(5,2)) as [%_change_total]
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(allocated_extent_page_count) as decimal(5,2)) as [%_change_allocated]
from sys.dm_db_file_space_usage;
go

backup database SmartBackupDemo to dev1;
go

select  sum(total_page_count) as total_pages
    ,   sum(allocated_extent_page_count) as modified_pages_allocated
    ,   sum(modified_extent_page_count) as modified_pages_total
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(total_page_count) as decimal(5,2)) as [%_change_total]
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(allocated_extent_page_count) as decimal(5,2)) as [%_change_allocated]
from sys.dm_db_file_space_usage;
go

/* t-log */

select  log_since_last_log_backup_mb
    ,   log_truncation_holdup_reason
from sys.dm_db_log_stats(db_id('SmartBackupDemo'));
go

backup log SmartBackupDemo to dev1;
go

select  log_since_last_log_backup_mb
    ,   log_truncation_holdup_reason
from sys.dm_db_log_stats(db_id('SmartBackupDemo'))
go


/* 1st step */

delete top(10000)
from T
where col1>200000;
go

select  log_since_last_log_backup_mb
    ,   log_truncation_holdup_reason
from sys.dm_db_log_stats(db_id('SmartBackupDemo'))
go

backup log SmartBackupDemo to dev1;
go

select  log_since_last_log_backup_mb
    ,   log_truncation_holdup_reason
from sys.dm_db_log_stats(db_id('SmartBackupDemo'))
go

/* smart differential backup script */

declare @diff_threshold_pct decimal(5,2) = 50.0;

if (    select  cast ( sum(modified_extent_page_count) * 100.0 / sum(allocated_extent_page_count) as decimal(5,2)) as [%_change_allocated]
        from sys.dm_db_file_space_usage ) > @diff_threshold_pct
begin
    backup database SmartBackupDemo to dev1 
end
else
begin
    backup database SmartBackupDemo to dev1 with differential
end
go

-- show full backup
restore headeronly from dev1;
go


delete top(10000)
from T
where col1>200000;
go

select  sum(total_page_count) as total_pages
    ,   sum(allocated_extent_page_count) as modified_pages_allocated
    ,   sum(modified_extent_page_count) as modified_pages_total
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(total_page_count) as decimal(5,2)) as [%_change_total]
    ,   cast ( sum(modified_extent_page_count) * 100.0 / sum(allocated_extent_page_count) as decimal(5,2)) as [%_change_allocated]
from sys.dm_db_file_space_usage;
go

declare @diff_threshold_pct_pct decimal(5,2) = 50.0;

if (    select  cast ( sum(modified_extent_page_count) * 100.0 / sum(allocated_extent_page_count) as decimal(5,2)) as [%_change_allocated]
        from sys.dm_db_file_space_usage ) > @diff_threshold_pct
begin
    backup database SmartBackupDemo to dev1 
end
else
begin
    backup database SmartBackupDemo to dev1 with differential
end
go

-- show diff backup
restore headeronly from dev1;
go


-- smrt t-log backup script

declare @log_threshold_mb int = 15.0;

while (1=1)
begin
    if (    select  log_since_last_log_backup_mb
            from sys.dm_db_log_stats(db_id('SmartBackupDemo')) ) >= @log_threshold_mb 
    begin
        backup log SmartBackupDemo to dev1
    end
end


-- show log backup and size
restore headeronly from dev1;
go

-- copy to another and run multiple times
delete top(10000)
from T
where col1>200000;
go

select  log_since_last_log_backup_mb
    ,   log_truncation_holdup_reason
from sys.dm_db_log_stats(db_id('SmartBackupDemo'))
go

restore headeronly from dev1;
go

//antonch

Antonios Chatzipavlis

Antonios Chatzipavlis is a highly experienced Data Solutions Consultant and Trainer. He has been working in the IT industry since 1988, holding various roles such as senior developer, IT Manager, Data & AI Solutions Architect and Consultant.

Since 1995, Antonios has focused on modern technologies and software development tools, primarily by Microsoft. He has specialized in Data & AI since 2000, with expertise in Microsoft Data Platform (SQL Server, Azure SQL Databases, Azure Synapse Analytics, Microsoft Fabric, Power BI, AI) and Databricks.

Antonios is also a Microsoft Certified Trainer (MCT) for over 25 years, has been recognized as a Microsoft Most Valuable Professional (MVP) in Data Platform since 2010 and he is in the Data Expert 40 Powerlist 2024 by Boussias. He is the co-founder and visionary behind XLYTiCA, a company dedicated to Data & AI solutions.