EC Edvaldo Castro Arquivo estatico do antigo WordPress

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

31 Jul 2018 · Backup, Tech, Virtual PASS BR · Backup, diff, differential backup, full, mssql, recuperabilidade, rpo, rto, sql, SQL SERVER, tlog

 

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

 
BackupTechVirtual PASS BR
Backupdiffdifferential backupfullmssqlrecuperabilidaderportosqlSQL SERVERtlog