go backarticles

Articles of SQLschool.gr Team

Smart backups in SQL Server 2017

Antonios Chatzipavlis





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


Relative Articles

Leave your comment

Login with your SQLschool.gr account if you want to comment on this article.


PASS chapter logo

The Official PASS Local Group for Greece

1434 33 595 27 41 1330
sql school greece logo
© 2010-2019 All rights reserved