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