Automatizando restores dos arquivos de backups

Hello galera, como têm passado? Estou passando bem rapinho apenas para compartilhar com vocês uma rotina administrativa bem legal que usamos em nossos ambientes para validar os arquivos gerados nos backups diários. Sabemos que a única forma de termos certeza se os backups são válidos para serem usados em ocasiões inoportunas, ou seja, em casos de desastre, é testando os mesmos na pratica através de restaurações periódicas. Pensando por esse ponto e da dificuldade que tínhamos em selecionar os arquivos para serem restaurados, decidimos elaborar uma stored procedure que pudêssemos agendar através de um job o tempo de testes dos arquivos. Levamos em consideração a lógica já usada pelo SQL Server, no qual restaura um banco de dados considerando a regra de menor esforço, entregando o banco de dados mais atualizada possível. A grande maioria dos ambientes que atuamos utiliza os seguintes tipos de backups:

  • Backup full diário;
  • Backup diferencial diário;
  • Backup de log diário.

Numa restauração de menor esforço o recomendado seria de restaurar na seguinte sequência:

  • Último backup full;
  • Último diferencial;
  • E os últimos backups de logs.

Assim teríamos um ambiente bem atualizado com baixíssima perda de dados.

Meu sócio então se dedicou a desenvolver uma procedure que passamos chamar de Sp_Restore. O objetivo seria de selecionar os arquivos mais recentes de backups e automatizar a restauração através de um job no SQLServerAgent. Tudo ficou fácil até mesmo na elaboração de um relatório mensal, onde o suporte de TI dos clientes passou a receber por email com todos os testes realizamos. Segue o script de criação da Sp_Restore abaixo.

Lembrando que é importante definir um login com permissão de leitura no banco msdb.

Version: 1.0
Data criação: 23/11/2017
Autor: Walmik Villas-Bôas (http://www.consultiba.com.br)

USE [DB_Consulti]
GO

/****** Object: StoredProcedure [dbo].[sp_Restore] Script Date: 04/16/2018 17:02:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_Restore]
@DBOrigem nvarchar(128)
, @DBDestino nvarchar(128)
, @CaminhoFull nvarchar(520)=”
, @CaminhoDiff nvarchar(520)=”
, @CaminhoLog nvarchar(520)=”
, @ServerRemoto nvarchar(128)=”
, @User nvarchar(128)=”
, @Pwd nvarchar(128)=”
AS
DECLARE @backup_set_id int, @media_set_id int, @backup_start_date datetime, @database_name nvarchar(256), @physical_device_name nvarchar(520), @type char(1)
DECLARE @ArqNome sysname, @ArqPath nvarchar(520)
DECLARE @TSQL nvarchar(4000)=”, @PATH nvarchar(4000)=N’ WITH REPLACE’, @Midia int=0, @Posicao int=0

DECLARE Caminho_Cursor CURSOR FOR
SELECT name, physical_name FROM sys.master_files WHERE database_id = db_id(@DBDestino) AND type IN (0,1) order by type desc, name asc
OPEN Caminho_Cursor;
FETCH NEXT FROM Caminho_Cursor
INTO @ArqNome, @ArqPath;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @Path=@Path + N’ , MOVE ”’ + @ArqNome + N”’ TO ”’ + @ArqPath + N””
FETCH NEXT FROM Caminho_Cursor
INTO @ArqNome, @ArqPath;
END
CLOSE Caminho_Cursor;
DEALLOCATE Caminho_Cursor;

DECLARE @StrSQL nvarchar(max), @cSQL nvarchar(max)
IF @ServerRemoto=”
BEGIN
SET @StrSQL='(SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type
FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id
WHERE bs.database_name=”’ + @DBOrigem + ”’ AND bs.type=”D” and bs.checkpoint_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=”’ + @DBOrigem + ”’ ORDER BY database_backup_lsn DESC))
UNION ALL
(SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type
FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id
WHERE bs.database_name=”’ + @DBOrigem + ”’ and bs.type=”I” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=”’ + @DBOrigem + ”’ ORDER BY database_backup_lsn DESC)
AND bs.checkpoint_lsn=(SELECT MAX(I.checkpoint_lsn) FROM (SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type, bs.checkpoint_lsn, bs.database_backup_lsn, bs.differential_base_lsn, bs.first_lsn, bs.last_lsn
FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id
WHERE bs.database_name=”’ + @DBOrigem + ”’ and bs.type=”I” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=”’ + @DBOrigem + ”’ ORDER BY database_backup_lsn DESC)) AS I))
UNION ALL
(SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type
FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id
WHERE bs.database_name=”’ + @DBOrigem + ”’ and bs.type=”L” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=”’ + @DBOrigem + ”’ ORDER BY database_backup_lsn DESC)
AND bs.checkpoint_lsn>=(SELECT bs.first_lsn FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id WHERE bs.database_name=”’ + @DBOrigem + ”’ and bs.type=”I” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=”’ + @DBOrigem + ”’ ORDER BY database_backup_lsn DESC)
AND bs.checkpoint_lsn=(SELECT MAX(I.checkpoint_lsn) FROM (SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type, bs.checkpoint_lsn, bs.database_backup_lsn, bs.differential_base_lsn, bs.first_lsn, bs.last_lsn FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id WHERE bs.database_name=”’ + @DBOrigem + ”’ and bs.type=”I” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=”’ + @DBOrigem + ”’ ORDER BY database_backup_lsn DESC)) AS I))
) ORDER BY backup_set_id’
SET @cSQL = ‘set @cursor = cursor forward_only static for ‘ + @StrSQL + ‘ open @cursor;’
END
ELSE
BEGIN
SET @StrSQL=’select b.* from openrowset(”SQLNCLI”, ”Server=’ + @ServerRemoto + ‘;UID=’ + @User + ‘;PWD=’ + @Pwd + ”’,”(SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type
FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id
WHERE bs.database_name=””’ + @DBOrigem + ””’ AND bs.type=””D”” and bs.checkpoint_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=””’ + @DBOrigem + ””’ ORDER BY database_backup_lsn DESC))
UNION ALL
(SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type
FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id
WHERE bs.database_name=””’ + @DBOrigem + ””’ and bs.type=””I”” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=””’ + @DBOrigem + ””’ ORDER BY database_backup_lsn DESC)
AND bs.checkpoint_lsn=(SELECT MAX(I.checkpoint_lsn) FROM (SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type, bs.checkpoint_lsn, bs.database_backup_lsn, bs.differential_base_lsn, bs.first_lsn, bs.last_lsn
FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id
WHERE bs.database_name=””’ + @DBOrigem + ””’ and bs.type=””I”” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=””’ + @DBOrigem + ””’ ORDER BY database_backup_lsn DESC)) AS I))
UNION ALL
(SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type
FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id
WHERE bs.database_name=””’ + @DBOrigem + ””’ and bs.type=””L”” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=””’ + @DBOrigem + ””’ ORDER BY database_backup_lsn DESC)
AND bs.checkpoint_lsn>=(SELECT bs.first_lsn FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id WHERE bs.database_name=””’ + @DBOrigem + ””’ and bs.type=””I”” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=””’ + @DBOrigem + ””’ ORDER BY database_backup_lsn DESC)
AND bs.checkpoint_lsn=(SELECT MAX(I.checkpoint_lsn) FROM (SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type, bs.checkpoint_lsn, bs.database_backup_lsn, bs.differential_base_lsn, bs.first_lsn, bs.last_lsn FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id WHERE bs.database_name=””’ + @DBOrigem + ””’ and bs.type=””I”” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=””’ + @DBOrigem + ””’ ORDER BY database_backup_lsn DESC)) AS I))
)”) AS b ORDER BY b.backup_set_id’
SET @cSQL = ‘set @cursor = cursor forward_only static for ‘ + @StrSQL + ‘ open @cursor;’
END

DECLARE @Restore_Cursor as cursor
exec sp_executesql @cSQL, N’@cursor cursor output’, @Restore_Cursor output
FETCH NEXT FROM @Restore_Cursor
INTO @backup_set_id, @media_set_id, @backup_start_date, @database_name, @physical_device_name, @type;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @Posicao=@Posicao+1
IF @type=’D’
BEGIN
IF @CaminhoFull=”
BEGIN
SET @TSQL=@TSQL + N’RESTORE DATABASE ‘ + @DBDestino + N’ FROM DISK = ”’ + @physical_device_name + N”” + @PATH
END
ELSE
BEGIN
SET @TSQL=@TSQL + N’RESTORE DATABASE ‘ + @DBDestino + N’ FROM DISK = ”’ + @CaminhoFull + REVERSE(LEFT(REVERSE(@physical_device_name),CHARINDEX( ‘\’,REVERSE(@physical_device_name))-1)) + N”” + @PATH
END
END
IF @type=’I’
BEGIN
IF @CaminhoDiff=”
BEGIN
SET @TSQL=@TSQL + N’RESTORE DATABASE ‘ + @DBDestino + N’ FROM DISK = ”’ + @physical_device_name + N””
END
ELSE
BEGIN
SET @TSQL=@TSQL + N’RESTORE DATABASE ‘ + @DBDestino + N’ FROM DISK = ”’ + @CaminhoDiff + REVERSE(LEFT(REVERSE(@physical_device_name),CHARINDEX( ‘\’,REVERSE(@physical_device_name))-1)) + N””
END
END
IF @type=’L’
BEGIN
IF @CaminhoLog=”
BEGIN
SET @TSQL=@TSQL + N’RESTORE LOG ‘ + @DBDestino + N’ FROM DISK = ”’ + @physical_device_name + N””
END
ELSE
BEGIN
SET @TSQL=@TSQL + N’RESTORE LOG ‘ + @DBDestino + N’ FROM DISK = ”’ + @CaminhoLog + REVERSE(LEFT(REVERSE(@physical_device_name),CHARINDEX( ‘\’,REVERSE(@physical_device_name))-1)) + N””
END
END
if @@CURSOR_ROWS<>@Posicao
BEGIN
if @type=’D’
BEGIN
SET @TSQL=@TSQL + N’ , NORECOVERY;’ + CHAR(10)
END
ELSE
BEGIN
SET @TSQL=@TSQL + N’ WITH NORECOVERY;’ + CHAR(10)
END
END
ELSE
BEGIN
if @type=’D’
BEGIN
SET @TSQL=@TSQL + N’ , RECOVERY;’ + CHAR(10)
END
ELSE
BEGIN
SET @TSQL=@TSQL + N’ WITH RECOVERY;’ + CHAR(10)
END
END
EXEC sp_executesql @TSQL;
–PRINT @TSQL
SET @TSQL=”
FETCH NEXT FROM @Restore_Cursor
INTO @backup_set_id, @media_set_id, @backup_start_date, @database_name, @physical_device_name, @type;
END
CLOSE @Restore_Cursor;
DEALLOCATE @Restore_Cursor;

GO

Script para agendar num job.

USE [DB_Consulti]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[sp_Restore]
@DBOrigem = N’BD_Origem’,
@DBDestino = N’BD_Destino’,
@CaminhoFull = N’H:\BACKUP DBs\BACKUPS DOS BANCOS\FULL\’,
@CaminhoDiff = N’H:\BACKUP DBs\BACKUPS DOS BANCOS\DIFF\’,
@CaminhoLog = N’H:\BACKUP DBs\BACKUPS DOS BANCOS\LOG\’,
@ServerRemoto = N’Ip_do_Servidor’,
@User = N’consulti’,
@Pwd = N’Senha_Login’

As variáveis @CaminhoFull, @CaminhoDiff e @CaminhoLog podem ser opcionais a depender da estrutura de cada ambiente se for uma restauração local.

Bem, acho que por hoje é só. Espero que aproveitem o script e aprimorem para compartilhar. Não esqueçam de destacar os méritos a meu sócio Walmik, pois é de criação dele a Sp_Restore.

Abraços à todos.

Anúncios

6 comentários sobre “Automatizando restores dos arquivos de backups

  1. Robert

    Bom dia Jéferson. Obrigado pela sua contribuição.
    Quais as versões do sql que são comparatives ?
    No meu caso está gerando vários erros ao criar a SP

    Obrigado

    1. Olá grande Robert. Atualmente utilizo nas versões 2008 R2, 2012 e 2014, porém não existem restrições, pois os objetos usados para construir a SP são comuns do banco msdb de todas as versões de SQL Server. Quais seriam os erros apresentados para facilitar o entendimento do problema?

  2. Robert

    Eu creio que seria a forma que estou preenchendo as informações diretórios dos backup , conforme esta linha @CaminhoFull nvarchar(520)= , Voce teria algum exemplo por gentileza ? Obrigado Jeferson

    1. Robert, o interessante seria informar qual a mensagem de retorno ao executar o SP_Proc, pois ficaria mais fácil lhe ajudar na solução. A configuração é relativa ao modelo de restore que deve adotar. Se o banco a ser restaurado e os arquivos que alocam os bakcups estiverem na mesma instância do banco de produção não é preciso declarar as variáveis @CaminhoFull, @CaminhoDiff e @CaminhoLog.

      Segue um exemplo:

      USE [Banco]
      GO
      DECLARE @return_value int
      EXEC @return_value = [dbo].[sp_Restore]
      @DBOrigem = N’Banco_Origem’,
      @DBDestino = N’Banco_Destino’,
      @CaminhoFull = N’H:\BACKUP DBs\BACKUPS DOS BANCOS\FULL\’,
      @CaminhoDiff = N’H:\BACKUP DBs\BACKUPS DOS BANCOS\DIFF\’,
      @CaminhoLog = N’H:\BACKUP DBs\BACKUPS DOS BANCOS\LOG\’,
      @ServerRemoto = N’IP_Server’,
      @User = N’Login_Server’,
      @Pwd = N’Senha_Login’
      GO

  3. Robert

    Bom dia Jeferson , segue saida.

    Obrigado

    Mensagem 102, Nível 15, Estado 1, Procedimento sp_Restore, Linha 14
    Incorrect syntax near ‘”’.
    Mensagem 102, Nível 15, Estado 1, Procedimento sp_Restore, Linha 23
    Incorrect syntax near ‘”’.
    Mensagem 319, Nível 15, Estado 1, Procedimento sp_Restore, Linha 23
    Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
    Mensagem 137, Nível 15, Estado 2, Procedimento sp_Restore, Linha 33
    Must declare the scalar variable “@Path”.
    Mensagem 137, Nível 15, Estado 2, Procedimento sp_Restore, Linha 41
    Must declare the scalar variable “@ServerRemoto”.
    Mensagem 105, Nível 15, Estado 1, Procedimento sp_Restore, Linha 43
    Unclosed quotation mark after the character string ‘(SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type
    FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id
    WHERE bs.database_name=”’ + @DBOrigem + ”’ AND bs.type=”D” and bs.checkpoint_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=”’ + @DBOrigem + ”’ ORDER BY database_backup_lsn DESC))
    UNION ALL
    (SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type
    FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id
    WHERE bs.database_name=”’ + @DBOrigem + ”’ and bs.type=”I” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=”’ + @DBOrigem + ”’ ORDER BY database_backup_lsn DESC)
    AND bs.checkpoint_lsn=(SELECT MAX(I.checkpoint_lsn) FROM (SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type, bs.checkpoint_lsn, bs.database_backup_lsn, bs.differential_base_lsn, bs.first_lsn, bs.last_lsn
    FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id
    WHERE bs.database_name=”’ + @DBOrigem + ”’ and bs.type=”I” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=”’ + @DBOrigem + ”’ ORDER BY database_backup_lsn DESC)) AS I))
    UNION ALL
    (SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type
    FROM msdb.dbo.backupmediafamily bf inner join msdb.dbo.backupset bs on bf.media_set_id=bs.media_set_id
    WHERE bs.database_name=”’ + @DBOrigem + ”’ and bs.type=”L” and bs.database_backup_lsn=(SELECT TOP 1 bf.database_backup_lsn FROM msdb.dbo.backupset bf WHERE bf.database_name=”’ + @DBOrigem + ”’ ORDER BY database_backup_lsn DESC)
    AND bs.checkp…
    Mensagem 102, Nível 15, Estado 1, Procedimento sp_Restore, Linha 43
    Incorrect syntax near ‘(SELECT bs.backup_set_id, bs.media_set_id, bs.backup_start_date, bs.database_name, bf.physical_device_name, bs.type
    FROM msdb.db’.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s