Deixando sua rotina de backup mais inteligente

Fala galera!

Tudo certo com vocês?

No post de hoje gostaria de compartilhar um novo recurso do SQL Server 2017 que é o Smart Backup e depois também foi disponibilizado no Service Pack 2 do SQL Server 2016.

Basicamente o que temos de novo é uma coluna na DMV (Dynamic Management View) sys.dm_db_file_space_usage chamada “modified_extent_page_count”. Essa coluna traz o número de páginas que foram alteradas desde o seu último backup full. Verificando essas contagens de páginas podemos deixar as nossas rotinas de backup mais inteligentes, pois o número de páginas modificadas pode ser usado para rastrear as alterações do banco de dados desde o último backup full e com isso decidir se o backup diferencial poderia ser o mais indicado nesse caso.

Normalmente utilizamos uma política de fazer um backup full no sábado ou domingo e backups diferenciais durante a semana. Utilizando essa estratégia tradicional, muitas vezes acontece de o backup diferencial de sexta-feira já estar quase do tamanho do backup full e tem casos que até maior, o que leva a um tempo maior em caso de necessidade de se fazer um restore.

Com essa nova feature podemos deixar as nossas rotinas de backup mais inteligentes validando se o percentual de páginas alteradas é 75% do total de páginas do bando de dados. Nesse caos, já valeria a pena fazer um backup full ao invés de um backup diferencial!

Vejamos na pratica como funciona:

Antes vamos realizar um backup full da nossa database para zerar o percentual de páginas alteradas.

Backup Database Northwind
To disk = 'D:\SQLSERVER\BKP\NorthWind_Dados_Full.bak'
with compression, format, stats=5

SELECT DB_NAME(database_id) DB_Name,
       total_page_count TotalPaginas,
       modified_extent_page_count TotalExtent_Modificado,
       CAST((modified_extent_page_count + mixed_extent_page_count) * 1.00 / allocated_extent_page_count AS NUMERIC(18, 2)) [%_PagModificadas]
FROM sys.dm_db_file_space_usage;

Como podemos observar o %_PagModificadas está zerado.

O script abaixo faz uma validação se o percentual de páginas alteradas é maior que 75% e se o dia da semana está entre segunda-feira e quinta-feira ou se é domingo. Se essa cláusula for verdadeira, a rotina vai executar um backup full, caso contrário, executa o backup diferencial.

DECLARE @threshold_bkp_full NUMERIC(18,2), @dia_semena int

SELECT @threshold_bkp_full =  CAST((modified_extent_page_count + mixed_extent_page_count) * 1.00 / allocated_extent_page_count AS NUMERIC(18, 2))
FROM sys.dm_db_file_space_usage;

SELECT @dia_semena = DATEPART(WEEKDAY,GETDATE())
													
-- 1 - Domingo
-- 2 - Segunda-feira
-- 3 - Terça-feira
-- 4 - Quarta-feira
-- 5 - Quinta-feira
-- 6 - Sexta-feira
-- 7 - Sábado

IF (@threshold_bkp_full > '0.75' AND @dia_semena in (2,3,4,5)) OR @dia_semena = 1

BEGIN 

BACKUP DATABASE Northwind
TO  DISK = 'D:\SQLSERVER\BKP\NorthWind_Dados_Full.bak'
WITH COMPRESSION,
     FORMAT,
     STATS = 5
PRINT 'Backup FULL'
END

ELSE
BEGIN 
BACKUP DATABASE Northwind
TO  DISK = 'D:\SQLSERVER\BKP\NorthWind_Dados_Dif.bak'
WITH COMPRESSION,
     FORMAT,
	 DIFFERENTIAL,
     STATS = 5
PRINT 'Backup Diferencial'
END

Sugerindo que esse backup full foi realizado em um Domingo, durante a Segunda-feira a empresa trabalhou normalmente e algumas páginas foram alteradas.

Podemos observar que na segunda-feira apenas 3% das páginas de dados foram alteradas. Com isso, a nossa rotina de backup vai realizar o backup diferencial, como é esperado.

Agora vamos simular que chegamos na Quinta-feira e já tivemos bastante alterações no banco, milhares de registros novos e milhares de update aconteceram. Com isso, ultrapassamos o nosso threshold de 75% de páginas modificadas.

Como podemos ver na Quinta-feira o nosso percentual de páginas alteradas chegou a 83%, com isso a nossa rotina vai executar um backup full.

Como foi dito na introdução do post, essa nova feature nos ajuda a otimizar a nossa estratégia de backups. O script de validação do % de páginas modificadas e dia da semana pode ser melhor customizados, no exemplo do post coloquei algo bem simples mesmo.

Bom pessoal a ideia desse post foi apresentar essa nova feature apesar de não ser novidade, pois o SQL Server 2019 já está saindo do forno e muitos ainda não conheciam.

Espero que tenham gostado.

Abraços,

Tiago Neves

Curta a minha página no Facebook.