Devido a existência de dúvidas constantes com procedimentos de backup no fórum MSDN e TECHNET, então, resolvi criar uma rotina customizada de backup para compartilhar com nossa comunidade de SQL Server.
O código, abaixo, avalia os arquivos existentes no local de armazenamento do backup e, de acordo com o período de retenção(permanência) dos backups, os arquivos que estiverem fora do escopo de retenção serão removidos e, em seguida, é iniciada a rotina de backup, sendo possível definir que tipo de backup será realizado (full, diferencial, log).
O exemplo, abaixo, reproduz uma das formas de realização de backups, podendo ser melhorada ou apenas servir de base para novas procedimentos.
Para usar o exemplo, basta executar o script abaixo para criar a procedure "PR_BACKUP" e chamá-lo por um job. No final do script existe um exemplo de como executar a procedure para a realização de backups.
/*#### CRIA PROCEDURE DE BACKUP CUSTOMIZADO ####*/
use
master
go
if
exists(select 1 from sysobjects where name = ‘pr_backup’)
drop
procedure pr_backup
go
create
proc pr_backup
@database
varchar(50), — Informe o database para backup
@localbackup
varchar(500) = ‘C:’ , — Caminho default de backup, caso não seja declarado na chamada da procedure.
@tipoBackup
INT = 1, — Seleciona o tipo de backup. 1 = FULL , 2 = DIFERENCIAL , 3 = LOG
@retencao
int = 30
as
/* DECLARA VARIÁVEIS DA ROTINA */
create
table #backups (arquivos varchar(500))
declare
@data varchar(12)
declare
@cmd varchar(500) — Número de dias que serão ignorados os backups anteriores
declare
@tipoBackup_name varchar(15)
declare
@dataremocao varchar(12)
set
@dataremocao=CONVERT(varchar(12),dateadd(day,- @retencao,getdate()),112)
if
@tipoBackup = 1
begin
SET @tipoBackup_name = ‘Full’
END
if
@tipoBackup = 2
begin
SET @tipoBackup_name = ‘Diferencial’
END
if
@tipoBackup = 3
begin
SET @tipoBackup_name = ‘Log’
END
— ROTINA REMOVE BACKUP MAIOR QUE X DIAS
set
@cmd
= ‘exec xp_cmdshell ‘+ ””+‘dir ‘+@localbackup+@database+‘-‘ +@tipoBackup_name+‘*.bkp’+””
insert
into
#backups
exec
(
@cmd)
declare
@arq
varchar(100)
declare
arq
cursor for
select
substring(substring(arquivos,charindex(@database,arquivos)+LEN(@database)+LEN(@tipoBackup_name)+1,(LEN(arquivos)-(charindex(@database,arquivos)+LEN(@tipoBackup_name)))),1,12) as arquivos from #backups
where
(
charindex(@database,arquivos)<>0 and charindex(@database,arquivos) is not null)
and
convert
(datetime,substring(substring(arquivos,charindex(@database,arquivos)+LEN(@database)+LEN(@tipoBackup_name)+1,(LEN(arquivos)-(charindex(@database,arquivos)+LEN(@tipoBackup_name)))),1,8)) <= @dataremocao
open
arq
fetch
next from arq into @arq
while
@@FETCH_STATUS = 0
begin
set
@cmd=‘xp_cmdshell ”del ‘+@localbackup+ @database+‘-‘+@tipoBackup_name+@arq+‘.bkp”’
EXEC
(@cmd)
fetch
next from arq into @arq
end
close
arq
deallocate
arq
/* ROTINA REALIZA BACKUP */
if
@tipoBackup = 1
begin
SET @tipoBackup_name = ‘Full’
set @data=CONVERT(varchar(14),GETDATE(),112)+REPLACE(CONVERT(VARCHAR(5),GETDATE(),108),‘:’,”)
set @cmd = ‘Backup database ‘+@database + ‘ to disk = ‘+””+@localbackup+”+@database+‘-‘+ @tipoBackup_name +@data+‘.bkp’+””
exec (@cmd)
print ”
print ”
print ‘Backup do database ‘+ @database + ‘Realizado em ‘+@localbackup+”+@database+‘-‘+@tipoBackup_name +@data+‘.bkp’
END
if
@tipoBackup = 2
begin
SET @tipoBackup_name = ‘Diferencial’
set @data=CONVERT(varchar(14),GETDATE(),112)+REPLACE(CONVERT(VARCHAR(5),GETDATE(),108),‘:’,”)
set @cmd = ‘Backup database ‘+@database + ‘ to disk = ‘+””+@localbackup +” +@database+‘-‘+ @tipoBackup_name +@data+‘.bkp’+”’ WITH DIFFERENTIAL’
exec (@cmd)
print ”
print ”
print ‘Backup do database ‘+ @database + ‘Realizado em ‘+@localbackup+” +@database+‘-‘+@tipoBackup_name +@data+‘.bkp’
END
if
@tipoBackup = 3
begin
SET @tipoBackup_name = ‘Log’
set @data=CONVERT(varchar(14),GETDATE(),112)+REPLACE(CONVERT(VARCHAR(5),GETDATE(),108),‘:’,”)
set @cmd = ‘Backup LOG ‘+@database + ‘ to disk = ‘+””+@localbackup+” +@database+‘-‘+ @tipoBackup_name +@data+‘.bkp’+””
exec (@cmd)
print ”
print ”
print ‘Backup do LOG do database ‘+ @database + ‘ Realizado em ‘+@localbackup+” +@database+‘-‘+@tipoBackup_name +@data+‘.bkp’
END
go
/*#### EXEMPLO DE COMO EXECUTAR PROCEDURE DE BACKUP ####*/
/*
exec
pr_backup
@database = ‘teste’, — Nome do database
@localbackup = ‘D:’, –Caso não seja declarado, o local do backup será em "C:"
@tipoBackup = 1, — 1 = Full, 2 = Diferencial, 3 = Log
@retencao = 7 –Dias de retenção de backups, caso esse parâmetro seja omitido o padrão é 30 dias.
*/
Observações:
Para backups diferenciais e de log, é necessário configurar o recovery model do banco para bulk-logged ou full.
Até a próxima….
Emanuel Peixoto.