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