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 is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.