Navigate
X

Backup Full Diário, sua melhor opção?

5/5 - (4 votes)

 

If you are not a native Portuguese speaker, or prefer to read it in another language, please scroll down the page and you will find a translator at the very right side.

 

Existem diversas opções para que uma determinada empresa independente de seu porte tenha uma polícia de backup bem definida, de conhecimento de todos e que seja seguida à risca, falei sobre isso no post: Política de backup – Implante esta ideia.

Para encontrar todos meus artigos sobre Backup, Restore e assuntos relacionados,  Clique aqui.

Das diversas opções e combinações possíveis com todos os tipos disponíveis de Backups no SQL Server, acredito que a opção em se fazer um backup full dos bancos todos os dias seja a menos efetiva e ainda pior, a que causa de certa forma, um desperdício de recursos de processamento e armazenamento.

Para comprovar o que estou afirmando no trecho anterior, vou realizar a seguir testes com as seguintes combinações (que já encontrei em muitas empresas que prestei serviços):

  • Backup Full diário (sem Backup Diferencial e sem Backup de Log)
  • Backup Full diário + Backup Diferencial no meio do dia.
  • Backup Full semanal + Backup Diferencial diário + Backup de Log a cada hora útil
  • Backup Full semanal + Backup de Log a cada hora útil.

Caso queira ir direto aos resultados dos testes, CLIQUE AQUI.

 

Sequência de testes realizados:

Para os exemplos, utilizarei o banco de dados StackOverflow2010, clique aqui para mais informações e download desse banco de dados de exemplo.

 

Teste 1:

Backup Full diário (sem Backup Diferencial e sem Backup de Log).

O script a seguir simula a criação de um Backup Full por dia, sem qualquer outro tipo de backup (Differential ou TLog) durante uma semana.

backup database StackOverflow2010 to disk = 'E:\Backup\StackOverflow2010_Segunda.bak' with stats = 1
backup database StackOverflow2010 to disk = 'E:\Backup\StackOverflow2010_Terca.bak' with stats = 1
backup database StackOverflow2010 to disk = 'E:\Backup\StackOverflow2010_Quarta.bak' with stats = 1
backup database StackOverflow2010 to disk = 'E:\Backup\StackOverflow2010_Quinta.bak' with stats = 1
backup database StackOverflow2010 to disk = 'E:\Backup\StackOverflow2010_Sexta.bak' with stats = 1
backup database StackOverflow2010 to disk = 'E:\Backup\StackOverflow2010_Sabado.bak' with stats = 1
backup database StackOverflow2010 to disk = 'E:\Backup\StackOverflow2010_Domingo.bak' with stats = 1

Resultado:

  • Tamanho total (semana): 59.9GB
  • Recuperabilidade: Somente no momento da conclusão do backup (backup_finish_date), sendo assim possível que haja a perda de até 1 dia de dados.
  • Comentário: Infelizmente já tive alguns clientes onde o ambiente era considerado altamente crítico, com uma rotina de backup como a supracitada com o argumento de que não poderiam perder dados, e por esse motivo, era realizado o backup full diário, sem qualquer backup adicional. Muito cuidado nessa estratégia.

 

Teste 2:

Backup Full diário + Backup Diferencial no meio do dia.

O script a seguir simula a criação de um Backup Full (D) todos os dias, e adicionalmente um Backup do tipo Differential (I)  que seria executado diariamente com 12h de intervalo para o Full anterior. Para esse e os demais testes, simularemos uma carga de 20.000 registros por dia nesse banco, o que será equivalente a 160MB de dados por dia (lembrando da proporcionalidade, estamos trabalhando aqui com uma base de dados menor que 10GB).

Atente-se à recuperabilidade explicada após os exemplos dos scripts nesse teste.

--Garantindo que seja um banco restaurado do zero, sem nenhuma alteração
USE master
GO
RESTORE DATABASE StackOverflow2010
FROM disk = 'E:\ToBeRestored\StackOverflow2010.bak'
WITH STATS
,MOVE 'StackOverflow2010' TO 'D:\MSSQL\Data\StackOverflow2010.mdf'
,MOVE 'StackOverflow2010_log' TO 'D:\MSSQL\Log\StackOverflow2010_log.ldf'
,REPLACE
GO

--Alterando a durabilidade das transações para que os inserts sejam mais rápidos.
ALTER DATABASE StackOverflow2010 SET DELAYED_DURABILITY = FORCED
GO

USE StackOverflow2010
GO
-- Criação de uma tabela para exemplos de crescimento da base de dados
DROP TABLE IF EXISTS dbo.TesteBackup
CREATE TABLE dbo.TesteBackup
(
	id INT IDENTITY
	,valor CHAR(8000) NOT NULL DEFAULT REPLICATE('A',8000)
)
GO

/******************* SEGUNDA FEIRA *******************/ 

--Backup Full Segunda-Feira (Propositalmente, não foi utilizado o Backup Compression)
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_FULL_Segunda.bak' WITH STATS
GO

--Inserção de 20000 registros para simular carga de dados
INSERT INTO dbo.TesteBackup DEFAULT VALUES
GO 20000

EXEC sys.sp_spaceused TesteBackup
--name			rows	reserved	data		index_size	unused
--TesteBackup	20000   160072 KB	160000 KB	8 KB		64 KB

--Backup do tipo Differential de Segunda Feira
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Segunda.bak' WITH DIFFERENTIAL, STATS
GO


/******************* TERÇA FEIRA *******************/ 
--Backup Full Terça-Feira 
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_FULL_Terca.bak' WITH STATS
GO

--Inserção de 20000 registros para simular carga de dados
INSERT INTO dbo.TesteBackup DEFAULT VALUES
GO 20000

EXEC sys.sp_spaceused TesteBackup
--name			rows	reserved	data		index_size	unused
--TesteBackup	40000   320200 KB	320000 KB	8 KB		192 KB

--Backup do tipo Differential de Terça-Feira
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Terca.bak' WITH DIFFERENTIAL, STATS
GO

/******************* QUARTA FEIRA *******************/
--Backup Full Quarta-Feira (Propositalmente, não foi utilizado o Backup Compression)
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_FULL_Quarta.bak' WITH STATS
GO
--Inserção de 20000 registros para simular carga de dados
INSERT INTO dbo.TesteBackup DEFAULT VALUES
GO 20000

EXEC sys.sp_spaceused TesteBackup
--name			rows	reserved	data		index_size	unused
--TesteBackup	60000   480264 KB	480000 KB	8 KB		256 KB


--Backup do tipo Differential de Quarta-Feira
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Quarta.bak' WITH DIFFERENTIAL, STATS
GO

/******************* QUINTA FEIRA *******************/
--Backup Full Quinta-Feira (Propositalmente, não foi utilizado o Backup Compression)
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_FULL_Quinta.bak' WITH STATS
GO
--Inserção de 20000 registros para simular carga de dados
INSERT INTO dbo.TesteBackup DEFAULT VALUES
GO 20000

EXEC sys.sp_spaceused TesteBackup
--name			rows	reserved	data		index_size	unused
--TesteBackup	80000	640264 KB	640000 KB	8 KB		256 KB


--Backup do tipo Differential de Quinta-Feira
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Quinta.bak' WITH DIFFERENTIAL, STATS
GO

/******************* SEXTA FEIRA *******************/
--Backup Full Sexta-Feira (Propositalmente, não foi utilizado o Backup Compression)
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_FULL_Sexta.bak' WITH STATS
GO
--Inserção de 20000 registros para simular carga de dados
INSERT INTO dbo.TesteBackup DEFAULT VALUES
GO 20000

EXEC sys.sp_spaceused TesteBackup
--name			rows	reserved	data		index_size	unused
--TesteBackup	100000  800328 KB	800000 KB	8 KB		320 KB


--Backup do tipo Differential de Sexta-Feira
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Sexta.bak' WITH DIFFERENTIAL, STATS
GO

/******************* SÁBADO *******************/
--Backup Full Sábado (Propositalmente, não foi utilizado o Backup Compression)
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_FULL_Sabado.bak' WITH STATS
GO
--Inserção de 20000 registros para simular carga de dados
INSERT INTO dbo.TesteBackup DEFAULT VALUES
GO 20000

EXEC sys.sp_spaceused TesteBackup
--name			rows	reserved	data		index_size	unused
--TesteBackup	120000  960328 KB	960000 KB	8 KB		320 KB

--Backup do tipo Differential de Sabado
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Sabado.bak' WITH DIFFERENTIAL, STATS
GO

/******************* DOMINGO *******************/
--Backup Full Domingo (Propositalmente, não foi utilizado o Backup Compression)
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_FULL_Domingo.bak' WITH STATS
GO
--Inserção de 20000 registros para simular carga de dados
INSERT INTO dbo.TesteBackup DEFAULT VALUES
GO 20000

EXEC sys.sp_spaceused TesteBackup
--name			rows	reserved	data		index_size	unused
--TesteBackup	140000	1120328 KB	1120000 KB	8 KB		320 KB

--Backup do tipo Differential de Domingo
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Domingo.bak' WITH DIFFERENTIAL, STATS
GO

Resultado:

  • Tamanho total (semana): 73.1GB
  • Recuperabilidade: Somente no ponto exato da conclusão de cada um dos backups (Full ou Differential), havendo dessa maneira a possibilidade real de perda de algumas horas de dados.
  • Comentário: Apesar de ter melhorado um pouco a recuperabilidade do ambiente (reduzindo a possibilidade de perda de dados de 1 dia para algumas horas), o espaço necessário para armazenar 1 semana de backup aumentou consideravelmente, em troca de pouca possibilidade de recuperação real dos dados, no momento da necessidade de um restore desses backups.

Teste 3:

Backup Full semanal + Backup Diferencial diário + Backup de Log a cada hora útil.

O script simula a execução de um backup Full uma vez por semana (nesse caso será criado o exemplo de 1 semana, logo apenas 1 Backup Full), um backup Differential uma vez ao dia e um backup do Log de Transações uma vez a cada hora durante as horas úteis do dia (consideremos horas úteis o período entre 8:00 e 18:00).

--Garantindo que seja um banco restaurado do zero, sem nenhuma alteração
USE master
GO
RESTORE DATABASE StackOverflow2010
FROM disk = 'E:\ToBeRestored\StackOverflow2010.bak'
WITH STATS
,MOVE 'StackOverflow2010' TO 'D:\MSSQL\Data\StackOverflow2010.mdf'
,MOVE 'StackOverflow2010_log' TO 'D:\MSSQL\Log\StackOverflow2010_log.ldf'
,REPLACE
GO

--Alterando a durabilidade das transações para que os inserts sejam mais rápidos.
ALTER DATABASE StackOverflow2010 SET DELAYED_DURABILITY = FORCED
GO

--Garantindo Recovery Model como Full para que possa ser realizado Backup do Transaction log.
ALTER DATABASE StackOverflow2010 SET RECOVERY FULL
GO

USE StackOverflow2010
GO
-- Criação de uma tabela para exemplos de crescimento da base de dados
DROP TABLE IF EXISTS dbo.TesteBackup
CREATE TABLE dbo.TesteBackup
(
	id INT IDENTITY
	,valor CHAR(8000) NOT NULL DEFAULT REPLICATE('A',8000)
)
GO

/******************* DOMINGO (01:00) *******************/ 

--Backup Full Domingo 23:00 (Propositalmente, não foi utilizado o Backup Compression)
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_FULL_Segunda.bak' WITH STATS
GO

/******************* SEGUNDA *******************/ 

--Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil
DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200)
WHILE @i < 19
BEGIN
	SET @j = 1
	WHILE @j < 2001
		BEGIN
			INSERT INTO dbo.TesteBackup DEFAULT VALUES
			SET @j = @j+1
		END
		SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Segunda_'+CAST(@i AS VARCHAR(2))+'.bak'''
		EXEC(@backupcmd)
		SET @i = @i+1
		PRINT @i
		
END	

--Backup do tipo Differential de SEGUNDA  23:00
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Segunda.bak' WITH DIFFERENTIAL, STATS
GO


/******************* TERÇA *******************/ 

--Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil
DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200)
WHILE @i < 19
BEGIN
	SET @j = 1
	WHILE @j < 2001
		BEGIN
			INSERT INTO dbo.TesteBackup DEFAULT VALUES
			SET @j = @j+1
		END
		SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Terca_'+CAST(@i AS VARCHAR(2))+'.bak'''
		EXEC(@backupcmd)
		SET @i = @i+1
END	

--Backup do tipo Differential de TERÇA 23:00
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Terca.bak' WITH DIFFERENTIAL, STATS
GO

/******************* QUARTA *******************/

--Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil
DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200)
WHILE @i < 19
BEGIN
	SET @j = 1
	WHILE @j < 2001
		BEGIN
			INSERT INTO dbo.TesteBackup DEFAULT VALUES
			SET @j = @j+1
		END
		SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Quarta_'+CAST(@i AS VARCHAR(2))+'.bak'''
		EXEC(@backupcmd)
		SET @i = @i+1
END	

--Backup do tipo Differential de QUARTA 23:00
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Quarta.bak' WITH DIFFERENTIAL, STATS
GO


/******************* QUINTA *******************/

--Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil
DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200)
WHILE @i < 19
BEGIN
	SET @j = 1
	WHILE @j < 2001
		BEGIN
			INSERT INTO dbo.TesteBackup DEFAULT VALUES
			SET @j = @j+1
		END
		SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Quinta_'+CAST(@i AS VARCHAR(2))+'.bak'''
		EXEC(@backupcmd)
		SET @i = @i+1
END	

--Backup do tipo Differential de QUINTA 23:00
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Quinta.bak' WITH DIFFERENTIAL, STATS
GO


/******************* SEXTA *******************/

--Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil
DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200)
WHILE @i < 19
BEGIN
	SET @j = 1
	WHILE @j < 2001
		BEGIN
			INSERT INTO dbo.TesteBackup DEFAULT VALUES
			SET @j = @j+1
		END
		SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Sexta_'+CAST(@i AS VARCHAR(2))+'.bak'''
		EXEC(@backupcmd)
		SET @i = @i+1
END	

--Backup do tipo Differential de SEXTA 23:00
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Sexta.bak' WITH DIFFERENTIAL, STATS
GO


/******************* SÁBADO *******************/

--Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil
DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200)
WHILE @i < 19
BEGIN	
	SET @j = 1
	WHILE @j < 2001
		BEGIN
			INSERT INTO dbo.TesteBackup DEFAULT VALUES
			SET @j = @j+1
		END
		SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Sabado_'+CAST(@i AS VARCHAR(2))+'.bak'''
		EXEC(@backupcmd)
		SET @i = @i+1
END	

--Backup do tipo Differential de SABADO 23:00
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_DIFF_Sabado.bak' WITH DIFFERENTIAL, STATS
GO

/******************* DOMINGO *******************/

--Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil
DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200)
WHILE @i < 19
BEGIN
	SET @j = 1
	WHILE @j < 2001
		BEGIN
			INSERT INTO dbo.TesteBackup DEFAULT VALUES
			SET @j = @j+1
		END
		SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Domingo_'+CAST(@i AS VARCHAR(2))+'.bak'''
		EXEC(@backupcmd)
		SET @i = @i+1
END	

--Nesse caso não teria o backup full no domingo, pois iniciar-se-ia uma um novo ciclo com o backup Full

 

Resultado:

  • Tamanho total (semana): 17.7 GB
  • Recuperabilidade: Como foi realizado uma rotina de backup contemplando Backups do Log de Transações, é possível recuperar o banco em qualquer ponto do tempo a partir de um Backup Full inicial (seja ele qual for), restaurando os backups de TLog na sequência (ou o primeiro backup Differential anterior ao ponto no tempo requerido).
  • Comentário: É possível afirmar com certeza que essa é uma boa estratégia (salvo exceções) de backup para base de dados críticas que suportam pouco ou zero perda de dados. Obviamente cada empresa tem suas particularidades e isso deve ser levado em conta no momento da implantação das políticas de Backup e Restore.

Teste 4:

Backup Full semanal + Backup de Log a cada hora útil.

O script a seguir é bem semelhante ao anterior (Teste 3) com a exceção que esse não simulará a execução de um backup Differential diário. Nesse caso teremos a simulação de um backup Full aos Domingos e backups de Transaction Log a cada hora, em horas úteis (considerando horas úteis o período entre 8:00 e 18:00).

--Garantindo que seja um banco restaurado do zero, sem nenhuma alteração
USE master
GO
RESTORE DATABASE StackOverflow2010
FROM disk = 'E:\ToBeRestored\StackOverflow2010.bak'
WITH STATS
,MOVE 'StackOverflow2010' TO 'D:\MSSQL\Data\StackOverflow2010.mdf'
,MOVE 'StackOverflow2010_log' TO 'D:\MSSQL\Log\StackOverflow2010_log.ldf'
,REPLACE
GO

--Alterando a durabilidade das transações para que os inserts sejam mais rápidos.
ALTER DATABASE StackOverflow2010 SET DELAYED_DURABILITY = FORCED
GO

--Garantindo Recovery Model como Full para que possa ser realizado Backup do Transaction log.
ALTER DATABASE StackOverflow2010 SET RECOVERY FULL
GO

USE StackOverflow2010
GO
-- Criação de uma tabela para exemplos de crescimento da base de dados
DROP TABLE IF EXISTS dbo.TesteBackup
CREATE TABLE dbo.TesteBackup
(
	id INT IDENTITY
	,valor CHAR(8000) NOT NULL DEFAULT REPLICATE('A',8000)
)
GO

/******************* DOMINGO (01:00) *******************/ 

--Backup Full Domingo 23:00 (Propositalmente, não foi utilizado o Backup Compression)
BACKUP DATABASE StackOverflow2010 TO DISK = 'E:\Backup\StackOverflow2010_FULL_Segunda.bak' WITH STATS
GO

/******************* SEGUNDA *******************/ 

--Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil
DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200)
WHILE @i < 19
BEGIN
	SET @j = 1
	WHILE @j < 2001
		BEGIN
			INSERT INTO dbo.TesteBackup DEFAULT VALUES
			SET @j = @j+1
		END
		SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Segunda_'+CAST(@i AS VARCHAR(2))+'.bak'''
		EXEC(@backupcmd)
		SET @i = @i+1
		PRINT @i
		
END	
GO

/******************* TERÇA *******************/ 

--Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil
DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200)
WHILE @i < 19
BEGIN
	SET @j = 1
	WHILE @j < 2001
		BEGIN
			INSERT INTO dbo.TesteBackup DEFAULT VALUES
			SET @j = @j+1
		END
		SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Terca_'+CAST(@i AS VARCHAR(2))+'.bak'''
		EXEC(@backupcmd)
		SET @i = @i+1
END	
GO

/******************* QUARTA *******************/

--Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil
DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200)
WHILE @i < 19
BEGIN
	SET @j = 1
	WHILE @j < 2001
		BEGIN
			INSERT INTO dbo.TesteBackup DEFAULT VALUES
			SET @j = @j+1
		END
		SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Quarta_'+CAST(@i AS VARCHAR(2))+'.bak'''
		EXEC(@backupcmd)
		SET @i = @i+1
END	
GO

/******************* QUINTA *******************/

--Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil
DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200)
WHILE @i < 19
BEGIN
	SET @j = 1
	WHILE @j < 2001
		BEGIN
			INSERT INTO dbo.TesteBackup DEFAULT VALUES
			SET @j = @j+1
		END
		SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Quinta_'+CAST(@i AS VARCHAR(2))+'.bak'''
		EXEC(@backupcmd)
		SET @i = @i+1
END	
GO

/******************* SEXTA *******************/

--Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil
DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200)
WHILE @i < 19
BEGIN
	SET @j = 1
	WHILE @j < 2001
		BEGIN
			INSERT INTO dbo.TesteBackup DEFAULT VALUES
			SET @j = @j+1
		END
		SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Sexta_'+CAST(@i AS VARCHAR(2))+'.bak'''
		EXEC(@backupcmd)
		SET @i = @i+1
END	
GO

/******************* SÁBADO *******************/

--Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil
DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200)
WHILE @i < 19
BEGIN	
	SET @j = 1
	WHILE @j < 2001
		BEGIN
			INSERT INTO dbo.TesteBackup DEFAULT VALUES
			SET @j = @j+1
		END
		SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Sabado_'+CAST(@i AS VARCHAR(2))+'.bak'''
		EXEC(@backupcmd)
		SET @i = @i+1
END	
GO

/******************* DOMINGO *******************/

--Inserção de 2000 registros para simular carga de dados e backup de T-Log a cada hora útil
DECLARE @i TINYINT = 1, @j INT = 1, @backupcmd VARCHAR(200)
WHILE @i < 19
BEGIN
	SET @j = 1
	WHILE @j < 2001
		BEGIN
			INSERT INTO dbo.TesteBackup DEFAULT VALUES
			SET @j = @j+1
		END
		SET @backupcmd = 'BACKUP LOG StackOverflow2010 TO DISK = ''E:\Backup\StackOverflow2010_TLOG_Domingo_'+CAST(@i AS VARCHAR(2))+'.bak'''
		EXEC(@backupcmd)
		SET @i = @i+1
END	
GO
--Nesse caso não teria o backup full no domingo, pois iniciar-se-ia uma um novo ciclo com o backup Full

Resultado:

  • Tamanho total (semana): 11.9GB
  • Recuperabilidade: Assim como na opção anterior, a geração dos backups do log de transações possibilita a restauração de backups em qualquer ponto do tempo, desde que iniciado com um backup Full e dando sequência aos demais backups de log até o ponto desejado.
  • Comentários: Esse é o backup com menor custo de armazenamento que também oferece uma recuperabilidade em qualquer ponto no tempo, uma possível desvantagem é que caso ocorra a perda ou corrupção de algum arquivo de backup do log de transações, não é possível prosseguir um restore após aquele ponto, caso não haja um backup Differential subsequente.

 

Resultados e Considerações Finais:

Cada um dos diversos tipos de backup disponíveis no SQL Server tem sua importância e função dentro de uma política / rotina de backup bem definida que funcione de acordo com as necessidades e criticidade das aplicações e do negócio suportado pelas bases de dados presentes nas instâncias do SQL Server.

Uma política / rotina de backup com um alto nível de excelência deve compreender a utilização conjunta desses diferentes tipos de backup para fornecer de forma mais barata, rápida e correta os requisitos supracitados (cabe aqui a menção a RTO (Tempo total para recuperação) e RPO (Suportabilidade para possíveis perdas de dados)).

Veja abaixo os números dos testes:

Para dar uma noção de grandeza, farei a alteração da medida em GB para TB.

  • Teste 1: Backup Full diário (sem Backup Diferencial e sem Backup de Log)
    • Tamanho total em uma semana….: 59.9 TB
    • Possibilidade de perda de dados..: Até 24 horas
    • Indicado para (Opinião pessoal)…: NÃO INDICADO
  • Teste 2: Backup Full diário + Backup Diferencial no meio do dia.
    • Tamanho total em uma semana: 73.1 TB
    • Possibilidade de perda de dados: 12 horas (dependendo do horário do backup)
    • Indicado para (Opinião pessoal): NÃO INDICADO
  • Teste 3: Backup Full semanal + Backup Diferencial diário + Backup de Log a cada hora útil
    • Tamanho total em uma semana: 17.7 TB
    • Possibilidade de perda de dados: Sem perda de Dados (nas CNTP)
    • Indicado para (Opinião pessoal): Ambientes de Produção, mesmo com consumo um pouco maior com relação ao Teste 4.
  • Teste 4: Backup Full semanal + Backup de Log a cada hora útil.
    • Tamanho total em uma semana: 11.9 TB
    • Possibilidade de perda de dados: Sem perda de Dados (nas CNTP)
    • Indicado para (Opinião pessoal): Ambientes de Produção, melhor utilização custo benefício da área de armazenamento do backup, desde que se tenha ciência da criticidade de manter a integridade dos arquivos de backup do log de transações.

Para concluir, em minha humilde opinião, o Backup Full Diário torna-se uma opção inadequada na grande maioria das situações (para ser sincero, eu não sei listar uma onde seria a melhor opção), visto que demanda um consumo maior de armazenamento e não garante a possibilidade de restauração no ponto do tempo, caso seja necessário.

Obrigado por chegar até aqui,

Caso discorde, concorde ou queira argumentar / perguntar, fique à vontade para compartilhar suas ideias nos comentários.

Abraço,

Edvaldo Castro

 

Tags: Backupdiffdifferential backupfullmssqlrecuperabilidaderportosqlSQL SERVERtlog
Edvaldo Castro:

View Comments (9)

  • Excelente sua dissertação, parabens pelo conteudo enxuto, abriu bastante minha mente sobre o assunto.
    Depois se puder fale sobre armazenamento de backups na nuvem, tambem existe como faze-los usando os padroes que vc falou?

  • Evaldo, eu agradeço muito o seu post.

    Eu não sabia como provar para a empresa onde estou no momento o quanto eles estão desperdiçando espaço e tudo mais e com esse post eu consigo informação suficiente para ser ouvido.

    O cara que montou os bkps que são feitos hoje não é gestor, ele é chefe e não sabe literalmente nada de TI, inclusive é contra tecnologia. E foi esse cara quem fez os scripts de bkps que serão alterados em breve com a ajuda desse post. :)

    Muito obrigado.

    • Olá Robson,
      Muito obrigado pelo seu comentario no post.
      Fico extremamente feliz em ler seu comentário e em saber que pude ajudá-lo. Espero que você tenha sucesso em seus argumentos para mostrar que na grande maioria das vezes, Backup Full todos os dias não necessariamente é a melhor estratégia de Backup.

      Abraço,

      Edvaldo Castro

  • Olá Eduardo, parabéns pelo artigo ficou show. Em relação ao always on em base grandes, digo 6TB a 10TB, o que você acha? e sobre o crescimento do log ?

    • Olá Silvestre,
      Muito obrigado pelo acesso e comentário.
      O AlwaysOn para bases muito grandes segue o mesmo conceito das bases menores, sendo que a diferença, é que se a base é muito transacional, você terá que monitorar a replicação das transações para as replicas secundárias e possívelmente executar um backup do log de transações com maior frequência.

      Como sempre as grandes respostas da vida são: 42 e Depende... Cada caso é um caso, mas no geral, vai exigir um acompanhamento mais de perto.

      Grande abraço,

      Edvaldo Castro

  • Obrigado Edvaldo pelo feedeback! Muito bom seu artigo me ajudou nessa situação que te relatei. Parabéns!

  • Edvaldo, e sobre realizar 'backup' copiando os arquivos .mdf e .ldf? Aqui onde trabalho o pessoal de infra adquiriu uma ferramenta onde a mesma a cada 1h faz copia do diferencial dos arquivos e sobe pra nuvens. Eu sou contra esse tipo de 'backup' mas eles disseram que investiram na ferramenta e que esse tipo de backup não é problema. Qual sua opinião sobre apenas copiar os arquivos .mdf e .ldf?

    • Olá Marcos, obrigado pela leitura e comentário.
      Partindo do princípio da definição de Backup como cópia de segurança, eu diria que sim, é válido. Olhando um pouco mais como um DBA e com recuperabilidade do ambiente de banco de dados, eu particularmente gosto de ter controle sobre como estão os backups e saber que posso contar com os mesmos quando precisar realizar um retore. Não sei qual é a ferramenta e nem como ela funciona, mas até onde sei, o Windows coloca um handle nos arquivos de Log e Dados para bancos de dados online, o que em teoria poderia impedir seu acesso. Se ainda não foi feito, eu sugereria testes rotineiros sobre as possibilidades de restore dessa modalidade que foi adotada aí pela sua infra.
      Mas sinceramente, não me agrada muito fazer backup do SQL Server copiando arquivos, tendo uma vasta gama de opções nativas que podem fazer muito mais e com muito mais garantia.

      Abraço

      Edvaldo Castro

Leave a Comment
Related Post
  1. Primeiro ano como DBA Expat na Irlanda

    If you are not a native Portuguese speaker, or prefer to read it in another language,…

  2. PLÁGIO – Sério mesmo?

    DISCLAIMER:  Esse texto tem um conteúdo em princípio agressivo, mas nada mais é do que…