Rotina de backup no SQL Server

Fala pessoal, tudo na paz?

Cliente novo na área, oportunidade de falar sobre um caso que vemos bastante.

Uma das primeiras atividades que faço em um check up, é avaliar como está configurada a rotina de backup, e estava agendada assim.

• Backup Full todo sábado às 21:00h (OK)
• Backup de Log todos os dias da semana às 22:00 (“Aceitável”).

Poderia ser melhor? Sim, bem melhor, mas atendia o cliente, talvez porque nunca precisou realmente restaurar uma base de dados crítica.

Mas então, fui avaliar o método que os backups estavam sendo realizados e havia realmente um problema crítico, cada arquivo gerado no backup de log substituía o anterior usando a cláusula with INIT.

Levantei a “bola” para o cliente informando quais problemas eu via ali, e indicando uma sugestão de rotina (ola.hallengren).

A reposta que tive foi: “Mas com o WITH INIT o SQL gera vários backups dentro do mesmo arquivo e conseguimos restaurar assim”.. OPS, será? Vamos ver abaixo como funciona.

Mas antes vou detalhar um pouco sobre tipos de backup, as opções e cláusulas presentes em um backup.

Tipos mais usados de backup:

• Full (completo)
• Diff (diferencial)
• Log (Log de transações)

Backup Full armazena todos os objetos e parte do log de transação para que o  banco de dados possa ser recuperado. É a base para os outros tipos de backups.

Backup Diferencial armazena todas as alterações desde o último full.

Backup de Log faz o backup do log transações. Esse tipo de backup não é permitido no modelo de recuperação Simples, portanto funciona no Full e Bulk-Logged. Esse backup muitas vezes desprezado, mas é importantíssimo para cenários de alta criticidade e com RPO (Recovery Point Objective: à quantidade aceitável de informação que pode-se perder, no caso de uma falha.) bem definido. Cada backup de log armazena apenas as alterações desde o último backup de LOG ou último full (caso nao tenha de logs).

Esclarecido cada tipo de backup, vamos a algumas opções.

• Copy-only
• File backups
• Partial backups
• Tail-log backups

Backup Full Copy-Only funciona como o backup full, porém não serve de base para os outros tipos de backups. Muito usado para a restauração de bases de produção para ambientes de homologação e desenvolvimento. Em uma estratégia de backup, um backup copy-only não quebra sua cadeia de backups.

Backup de Arquivos ou File backups, é relevante para bancos de dados do SQL Server que contenham vários datafiles ou filegroups. Esse tipo de backup pode ser combinado com backups parciais.

O backup parcial é um dos métodos menos usados ​​no SQL Server. Todos os modelos de recuperação (simple, full, bulk-logged) suportam backups parciais, esse tipo visa melhorar a flexibilidade ao fazer backup de grandes bancos de dados que contêm grupos de arquivos somente leitura (Read only). Na instrução de backup utilizamos a opção READ_WRITE_FILEGROUPS para processar o backup de grupos de arquivos de leitura e gravação.

Tail Log Backup é um backup de log realizado (normalmente) após uma falha no banco de dados. Esse processo faz o backup do seu transaction log disponível desconsiderando os erros, devido a cláusula CONTINUE_AFTER_ERROR e força o fechamento o banco de dados para iniciar o processo de recuperação.

Bom, agora sim estamos quase chegando onde eu gostaria, as opções de backup.

• COMPRESSION: Esta opção ativa a compactação de backup na instrução de backup. NO_COMPRESSION desativa explicitamente a compactação de backup. Sugiro habilitar a propriedade de compressão de backups por padrão.
• ENCRYPTION: Um algoritmo de criptografia pode ser especificado com BACKUP para proteger os arquivos de backup armazenados externamente.
• MIRROR: Essa opção permite a você espelhar seu arquivo de backup em vários devices.

Agora vou descrever algumas das opções que citei lá em cima e que gerou dúvida na rotina de backup desse cliente.

FORMAT: Essa opção é usada para especificar se deve substituir as informações do cabeçalho da mídia. A cláusula FORMAT criará um novo conjunto de backup de mídia, enquanto o NOFORMAT preservará todas as informações.

INIT:  é usado para criar um novo conjunto de backups;

NOINIT: é usado para anexar o backup ao conjunto de backup existente.  É a opção padrão quando nada é especificado.

NAME: O parâmetro NAME é usado para identificar o conjunto de backup.

SKIP: O parâmetro skip é usado para pular a verificação de expiração no conjunto de backup.

NOREWIND: Este parâmetro é usado para manter um dispositivo de fita aberto e pronto para uso.

NOUNLOAD: este parâmetro é usado para instruir o SQL Server a não descarregar a fita da unidade após a conclusão da operação de backup.

STATS: A opção STATS é útil para obter o status da operação de backup em estágios regulares de seu progresso.

Segue uma demo, com uma série de scripts (mais utilizados) para vocês avaliarem cada comando em um ambiente de testes.

/*
-- CONFIGURAÇÃO
use master

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'sqlbackup'
GO

DROP DATABASE [sqlbackup]
GO


CREATE DATABASE sqlbackup ON PRIMARY
( NAME = N'sqlbackup_data_1',
FILENAME = N'c:\temp\sqlbackup_data_1.mdf' ,
SIZE = 20480KB , FILEGROWTH = 2048KB ),
FILEGROUP [Secondary] 
( NAME = N'sqlbackup_data_2',
FILENAME = N'c:\temp\sqlbackup_data_2.mdf' ,
SIZE = 20480KB , FILEGROWTH = 2048KB )
LOG ON
( NAME = N'sqlbackup_Log',
FILENAME = N'c:\temp\sqlbackup_log.ldf' ,
SIZE = 10240KB , FILEGROWTH = 10240KB)
GO

use sqlbackup

if object_id('temp_bkp') is not null
drop table temp_bkp


CREATE TABLE dbo.temp_bkp
(id int NOT NULL IDENTITY (1, 1), 
obs varchar(200) NULL,
tipo varchar(11) NULL,
media_set_id int NOT NULL,
backupmediaset_name nvarchar(128) NULL,
backupset_name nvarchar(128) NULL,
physical_device_name nvarchar(260) NULL,
backup_sizeMB decimal(10, 2) NULL,
position int NULL,
backup_start_date datetime NULL,
backup_finish_date datetime NULL,
is_copy_only bit NULL,
is_compressed bit NULL
) ON [PRIMARY]
go

create view vw_backups 
as
select case bs.type when 'D' then 'Full' when 'I' then 'Diferencial' when 'L' then 'Log' end as tipo, bms.media_set_id, bms.name backupmediaset_name, bs.name backupset_name, bmf.physical_device_name, CAST ( bs.backup_size / 1024.0 / 1024 AS DECIMAL(10, 2) ) as backup_sizeMB, bs.position, bs.backup_start_date, bs.backup_finish_date, bs.is_copy_only, is_compressed
from msdb.dbo.backupset bs
inner join msdb.dbo.backupmediaset bms on bms.media_set_id = bs.media_set_id
inner join msdb.dbo.backupmediafamily bmf on bmf.media_set_id = bs.media_set_id
where database_name = 'sqlbackup' and bs.backup_start_date = (select max(backup_start_date) from msdb.dbo.backupset b where b.database_name = bs.database_name)

*/

ALTER DATABASE sqlbackup SET RECOVERY FULL

------------------------------------------
-- BACKUP FULL

BACKUP DATABASE sqlbackup
To DISK='c:\temp\bkp\sqlbackup.BAK'
WITH FORMAT, 
INIT,
COMPRESSION,
MEDIANAME = 'sqlbackupmedia',
NAME = 'Full-sqlbackup backup';

insert into temp_bkp (obs,tipo,media_set_id,backupmediaset_name,backupset_name,physical_device_name,backup_sizeMB,position,backup_start_date,backup_finish_date,is_copy_only,is_compressed)
select 'Um backup full na posicao 1 do media set', * from vw_backups -- Um backup full na posicao 1 do media set
go
waitfor delay '00:00:01' 
------------------------------------------
-- BACKUP FULL COM MAIS DE UM BACKUP NO MESMO CONJUNTO DE BACKUP (MEDIA SET)

BACKUP DATABASE sqlbackup
To DISK='c:\temp\bkp\sqlbackup.BAK'
WITH COMPRESSION,
NOINIT, -- Opcao que permite manter mais de um arquivo dentro do mesmo conjunto de backups
MEDIANAME = 'sqlbackupmedia',
NAME = 'Full-sqlbackup backup';

insert into temp_bkp (obs,tipo,media_set_id,backupmediaset_name,backupset_name,physical_device_name,backup_sizeMB,position,backup_start_date,backup_finish_date,is_copy_only,is_compressed)
select 'Mais de um backup dentro do mesmo conjunto de backups (media_set), veja pela posicao e media_set_id', * from vw_backups 
go
waitfor delay '00:00:01' 
------------------------------------------
-- BACKUP STRIPING

BACKUP DATABASE sqlbackup TO 
DISK = 'c:\temp\bkp\sqlbackup1.BAK', 
DISK = 'c:\temp\bkp\sqlbackup2.BAK', 
DISK = 'c:\temp\bkp\sqlbackup3.BAK'
WITH FORMAT, 
COMPRESSION, 
NAME = 'FULL sqlbackup_distruibuido backup', STATS = 5

insert into temp_bkp (obs,tipo,media_set_id,backupmediaset_name,backupset_name,physical_device_name,backup_sizeMB,position,backup_start_date,backup_finish_date,is_copy_only,is_compressed)
select 'Um backup full na posicao 1 dividido por 3 arquivos', * from vw_backups 
go
waitfor delay '00:00:01' 
------------------------------------------
-- BACKUP ESPELHADO

BACKUP DATABASE sqlbackup 
TO DISK = 'c:\temp\bkp\sqlbackupmirror1.BAK'
MIRROR TO DISK = 'C:\temp\sqlbackupmirror2.BAK'
WITH COMPRESSION, 
INIT,
FORMAT,
MEDIANAME = 'sqlbackupmediamirror',
NAME = 'Full-sqlbackup-mirror backup';

insert into temp_bkp (obs,tipo,media_set_id,backupmediaset_name,backupset_name,physical_device_name,backup_sizeMB,position,backup_start_date,backup_finish_date,is_copy_only,is_compressed)
select 'Um backup full espelhado em dois arquivos', * from vw_backups 
go
waitfor delay '00:00:01' 
------------------------------------------
-- BACKUP DIFERENCIAL

BACKUP DATABASE sqlbackup
To DISK='c:\temp\bkp\sqlbackup_diff.BAK'
WITH COMPRESSION,
DIFFERENTIAL,
MEDIANAME = 'sqlbackupmedia',
NAME = 'Diff-sqlbackupmedia backup';

insert into temp_bkp (obs,tipo,media_set_id,backupmediaset_name,backupset_name,physical_device_name,backup_sizeMB,position,backup_start_date,backup_finish_date,is_copy_only,is_compressed)
select 'Backup differencial - Nova media_set_id ', * from vw_backups 
go
waitfor delay '00:00:01' 
------------------------------------------
-- BACKUP DE LOG

BACKUP LOG sqlbackup
To DISK='c:\temp\bkp\sqlbackup_log.trn'
WITH 
COMPRESSION,
INIT, 
MEDIANAME = 'sqlbackupLog',
NAME = 'Log-sqlbackup backup';
GO

insert into temp_bkp (obs,tipo,media_set_id,backupmediaset_name,backupset_name,physical_device_name,backup_sizeMB,position,backup_start_date,backup_finish_date,is_copy_only,is_compressed)
select 'backup de log dentro em um novo conjunto de backups (media_set), ver position', * from vw_backups -- 
go
waitfor delay '00:00:01' 
------------------------------------------
-- BACKUP DE LOG
BACKUP LOG sqlbackup
To DISK='c:\temp\bkp\sqlbackup_log.trn'
WITH 
COMPRESSION,
NOINIT, -- Mais de um backup dentro do mesmo conjunto de backups (media_set), veja pela posicao
MEDIANAME = 'sqlbackupLog',
NAME = 'Log-sqlbackup backup';
GO

insert into temp_bkp (obs,tipo,media_set_id,backupmediaset_name,backupset_name,physical_device_name,backup_sizeMB,position,backup_start_date,backup_finish_date,is_copy_only,is_compressed)
select 'Mais de um backup dentro do mesmo conjunto de backups (media_set), ver position', * from vw_backups -- 
go
waitfor delay '00:00:01' 

------------------------------------------
-- BACKUP PARCIAL

-- Alterando um file group para readonly
ALTER DATABASE sqlbackup MODIFY FILEGROUP [Secondary] READONLY


BACKUP DATABASE sqlbackup READ_WRITE_FILEGROUPS
TO DISK = N'c:\temp\bkp\sqlbackup_Backup_parcial.bak'
GO


insert into temp_bkp (obs,tipo,media_set_id,backupmediaset_name,backupset_name,physical_device_name,backup_sizeMB,position,backup_start_date,backup_finish_date,is_copy_only,is_compressed)
select 'Backup parcial - apenas read_write_filegroups', * from vw_backups -- 
go
waitfor delay '00:00:01' 

------------------------------------------
-- BACKUP TAIL LOG

BACKUP LOG sqlbackup
TO DISK = 'c:\temp\bkp\sqlbackup_taillog.log'
WITH CONTINUE_AFTER_ERROR,
MEDIANAME = 'sqlbackupmediatail',
NAME = 'taillog-sqlbackup backup';

insert into temp_bkp (obs,tipo,media_set_id,backupmediaset_name,backupset_name,physical_device_name,backup_sizeMB,position,backup_start_date,backup_finish_date,is_copy_only,is_compressed)
select 'Tail Log backup', * from vw_backups 
go


select * from temp_bkp

1º Verifique se vc nao possui um banco de dados sqlbackup.

2º Se vc nao possuir, é só criá-lo com o script comentado e depois executar tudo. Você terá um retorno como a imagem abaixo:

3º Você pode manipular esses scripts e executar passo a passo para fazer seus testes. 🙂

Beleza, entendi sobre backup, mas como posso pensar em uma estratégia de backup e restauração?

Recomendo a leitura do artigo do Edvaldo Castro em https://edvaldocastro.com/backup-full-diario/  , ele apresenta vários cenários.

De quebra um script para você acompanhar o percentual concluído do backup ou restore e a estimativa de conclusão durante a execução.

SELECT session_id, command, a.text, start_time, percent_complete, 
dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r 
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

Não tenho uma pessoa para validar e/ou implementar minha rotina de backup!

Aí pode falar comigo mesmo. 🙂

Por enquanto é isso pessoal, abraços!

Mais detalhes sobre backup aqui.

 

Publicidade