Estratégia de Backup | Restore

No post de hoje vou mostrar como montar uma estratégia de backup com FULL, DIFF e LOGS para o SQL Server.

Utilizo esse modelo em vários servidores e sempre atendeu a minha necessidade, acredito que irá servir para você também, caso não sirva, sinta-se à vontade para adaptá-lo a sua necessidade.

Bom vamos lá.

O modelo consiste em um backup Full, alguns Diferenciais e vários de Log de transação ao longo do dia.

Para facilitar segue uma tabelinha que você pode utilizar para montar a sua própria rotina.

Para o nosso teste irei utilizar o banco de dados AdventureWorks2014, afim de deixar o processo mais enxuto.

Antes de mais nada devemos alterar o Recovery Model do banco de dados para FULL, na verdade o banco tem que estar com o Recovery FULL antes de iniciar os backups Diferencias e de Logs, porém para o exemplo podemos deixar assim.

ALTER DATABASE AdventureWorks2014 SET RECOVERY FULL

Feito esse processo podemos iniciar a rotina com o primeiro backup que será o FULL, esse backup geralmente é realizado uma única vez ao dia, durante um horário em que o servidor esteja ocioso.

--FULL
DECLARE @nome VARCHAR(50)	  -- Nome do banco de dados
DECLARE @diretorio VARCHAR(256)   -- Diretório onde será gravado os backups
DECLARE @nomearquivo VARCHAR(256) -- Armazena o nome final do backup
DECLARE @dataarquivo VARCHAR(20)  -- Será armazenado a data do arquivo

SET @diretorio = 'C:\Backup\'
SELECT @dataarquivo = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(6),GETDATE(),108),':','')

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('AdventureWorks2014')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @nome

WHILE @@FETCH_STATUS = 0
BEGIN
SET @nomearquivo = @diretorio + @nome + '_' + @dataarquivo + '.full'
BACKUP DATABASE @nome TO DISK = @nomearquivo

FETCH NEXT FROM db_cursor INTO @nome
END

CLOSE db_cursor
DEALLOCATE db_cursor

Feito o backup FULL, podemos iniciar os backups diferenciais e de logs intercalando os horários. A cada 3 horas um diferencial e de meia em meia hora um de log, dessa forma teremos um cenário muito parecido com o da tabelinha acima.

--DIFERENCIAL
DECLARE @nome VARCHAR(50)	  -- Nome do banco de dados
DECLARE @diretorio VARCHAR(256)   -- Diretório onde será gravado os backups
DECLARE @nomearquivo VARCHAR(256) -- Armazena o nome final do backup
DECLARE @dataarquivo VARCHAR(20)  -- Será armazenado a data do arquivo

SET @diretorio = 'C:\Backup\'
SELECT @dataarquivo = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(6),GETDATE(),108),':','')

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('AdventureWorks2014')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @nome

WHILE @@FETCH_STATUS = 0
BEGIN
SET @nomearquivo = @diretorio + @nome + '_' + @dataarquivo + '.diff'
BACKUP DATABASE @nome TO DISK = @nomearquivo WITH DIFFERENTIAL

FETCH NEXT FROM db_cursor INTO @nome
END

CLOSE db_cursor
DEALLOCATE db_cursor

--LOG
DECLARE @nome VARCHAR(50)	  -- Nome do banco de dados
DECLARE @diretorio VARCHAR(256)   -- Diretório onde será gravado os backups
DECLARE @nomearquivo VARCHAR(256) -- Armazena o nome final do backup
DECLARE @dataarquivo VARCHAR(20)  -- Será armazenado a data do arquivo

SET @diretorio = 'C:\Backup\'
SELECT @dataarquivo = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(6),GETDATE(),108),':','')

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('AdventureWorks2014')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @nome

WHILE @@FETCH_STATUS = 0
BEGIN
SET @nomearquivo = @diretorio + @nome + '_' + @dataarquivo + '.trn'
BACKUP LOG @nome TO DISK = @nomearquivo

FETCH NEXT FROM db_cursor INTO @nome
END

CLOSE db_cursor
DEALLOCATE db_cursor

Dessa forma poderíamos configurar um cenário da seguinte forma:

1 – Job de backup FULL – 00:30

2 – Job de backup Diferencial com 4 schedules.

08:00 – 11:00 – 14:00 – 17:00

3 – Job de backup de Log também com 4 schedules com execuções a cada 30 minutos entre as execuções dos Diferenciais.

( 08:30 – 10:30 ) | (11:30 – 13:30) | (14:30 – 16:30) | (17:30 – 19:00)

Na imagem abaixo dá pra se ter uma ideia de como irão ficar os backups.

Agora que temos os backups realizados, podemos utilizar o script abaixo para montar o comando de restore de uma forma rápida.

No meu caso, foi criado um report no SQL Server Reporting Services onde eu passo os parâmetros para a geração do Script, como data que sera restaurado e o caminho onde estão localizados os backups.

SET NOCOUNT ON

DECLARE @dbName SYSNAME
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @cmdList TABLE (cmdlist NVARCHAR(500))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @lastLogBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
DECLARE @datahoracorte NVARCHAR(500)
DECLARE @qtdebkplog NVARCHAR(500)
DECLARE @cmdFull NVARCHAR(500)
DECLARE @cmdDiff NVARCHAR(500)
DECLARE @cmdLog NVARCHAR(500)

SET @dbName = 'AdventureWorks2014'
SET @backupPath = 'C:\Backup\'
SET @datahoracorte = '201810241500'
SET @cmd = 'DIR /b ' + @backupPath
SET @datahoracorte = @dbName + '_' + @datahoracorte

INSERT INTO @fileList (backupFile)
EXEC master.sys.xp_cmdshell @cmd

SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.full'
	AND backupFile LIKE @dbName + '%'

SELECT @lastDiffBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.diff'
	AND backupFile LIKE @dbName + '%'
	AND backupFile > @dbName
	AND backupFile < @datahoracorte

SELECT @lastLogBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.trn'
	AND backupFile LIKE @dbName + '%'
	AND backupFile > @dbName
	AND backupFile > @lastDiffBackup
	AND backupFile < @datahoracorte

SET @cmdFull = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = ''' + @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE'

INSERT INTO @cmdlist
VALUES (@cmdFull)

IF @lastDiffBackup IS NOT NULL
	AND @lastLogBackup IS NULL
BEGIN
	SET @cmdDiff = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = ''' + @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'

	INSERT INTO @cmdlist
	VALUES (@cmdDiff)

	SET @lastFullBackup = @lastDiffBackup
END

IF @lastDiffBackup IS NOT NULL
	AND @lastLogBackup IS NOT NULL
BEGIN
	SET @cmdDiff = 'RESTORE LOG ' + @dbName + ' FROM DISK = ''' + @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'

	INSERT INTO @cmdlist
	VALUES (@cmdDiff)

	SET @lastFullBackup = @lastDiffBackup

	SELECT @qtdebkplog = COUNT(1)
	FROM @fileList
	WHERE backupFile LIKE '%.trn'
		AND backupFile LIKE @dbName + '%'
		AND backupFile > @lastDiffBackup
		AND backupFile < @datahoracorte
END

DECLARE backupFiles CURSOR
FOR
SELECT backupFile
FROM @fileList
WHERE backupFile LIKE '%.trn'
	AND backupFile LIKE @dbName + '%'
	AND backupFile > @lastDiffBackup
	AND backupFile < @datahoracorte

OPEN backupFiles

FETCH NEXT
FROM backupFiles
INTO @backupFile

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @qtdebkplog = 1
		SET @cmdLog = 'RESTORE LOG ' + @dbName + ' FROM DISK = ''' + @backupPath + @backupFile + ''' WITH RECOVERY'

	IF @qtdebkplog > 1
		SET @cmdLog = 'RESTORE LOG ' + @dbName + ' FROM DISK = ''' + @backupPath + @backupFile + ''' WITH NORECOVERY'

	INSERT INTO @cmdlist
	VALUES (@cmdLog)

	SET @qtdebkplog = @qtdebkplog - 1

	FETCH NEXT
	FROM backupFiles
	INTO @backupFile
END

CLOSE backupFiles

DEALLOCATE backupFiles

SELECT *
FROM @cmdList

O script irá montar o comando de restore conforme a data passada para a variável @datahoracorte. No script acima foi definida a data 201810241500 e como podemos notar o comando de restore foi até o ultimo arquivo de log antes das 15:00.

Por fim podemos criar um outro Job para apagar os backups diferenciais e de logs ao final do dia.

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
--'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
--'Insira os diretórios onde estão localizados os arquivos de backup'
--'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

DECLARE @cmdDiff NVARCHAR(500)
DECLARE @cmdLog NVARCHAR(500)

SET @cmdDiff = 'del C:\Backups\*.diff' 
SET @cmdLog = 'del C:\Backups\*.trn' 

EXEC master.sys.xp_cmdshell @cmdDiff
EXEC master.sys.xp_cmdshell @cmdLog