Automatizando Restauração de Banco de Dados

Olá pessoal, tudo bem?

Essa semana recebi uma demanda de um cliente, no qual o mesmo me pediu uma base pra realizar testes em sua aplicação. Até ai tudo bem, porém se não fosse a seguinte exigência: A base teria que está atualizada toda semana.

Levando em consideração que é bem dificil atualmente realizar restores periodicos, até porque as bases de dados são grandes fisicamente, resolvi analisar e configurar da seguinte forma para resolver a demanda.

1 – Configurei os Backups Devices para os Backups de Logs e Full;

    **Device_Full – setado pra diretório do servidor secundário

    **Device_Logs – setado pra diretório do servidor secundário

2 – Configurei os Linkserver entre os servidores participantes;

    **Apenas no Servidor Secundário pra o servidor principal

3 – Configurei o stored_procedure de restauração na base master ou dbadmin;

    **Apenas no servidor secundário

4 – Configurei os jobs de criação da tabela de restore, exclusão da tabela e restauração.

    **Apenas no servidor secundário

Ressalto que a referência usada do script de restore automático foi extraida do site da Microsoft da contribuição dada pelo colega Luan Moreno no qual apenas realizei algumas adaptações pra adequar a realidade da empresa.

Como no cenário do cliente existem dois servidores, sendo um de produção e outro de backup, onde o secundário tem uma base de dados dbadmin que monitora e condiciona informações de controle sobre o ambiente de dados decidir colocar a stored procedure de restore nesta base.

As imagens mostro como ficou o cenário, no qual é possível agendar pra serem executados periodicamente.

 

1

Figura 1 – Apresenta a stored já criada numa base de acompanhamento. No final deste post irei disponibilizar o script da procedure.

 

2

Figura 2 – Apresenta os jobs já criados no servidor de backup, no qual serão usados para executar a operação de restauração.

 

3  Figura 3 – Apresenta o ligação feita entre o servidor de backup pra o de produção.

 

4    Figura 4 – Apresenta o plano de manutenção de backup no servidor produção.

 

Infelizmente não deu pra explanar mais detalhadamente sobre a configuração, mas espero ter ajudado com as dicas. 

Até a próxima!

Anexos:

USE [DBAdmin]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

–Procedure Restore Automático
CREATE PROCEDURE [dbo].[sp_restore]

–declaração de variáveis entrada de informações
@nome_db NVARCHAR(30),
@Move BIT
AS

–declaração de variáveis
DECLARE @bkp_ini DATETIME
DECLARE @backup_set_id_inicio INT
DECLARE @backup_set_id_fim INT

–capturando o último backup
SELECT @backup_set_id_inicio = MAX(backup_set_id) –inicialização da variável backup_set_id_inicio
FROM  [SRVBD\PRODUCTION].msdb.dbo.backupset
WHERE database_name = @nome_db AND type = ‘D’
–fechando o restore com WITH RECOVERY
IF @backup_set_id_fim IS NULL SET @backup_set_id_fim = 999999999 ;

–restore automático
WITH Backups AS
(
SELECT
    backup_set_id,
    b.type,
    CASE
        WHEN B.type IN (‘D’, ‘I’) THEN ‘RESTORE DATABASE AdventureWorks_teste FROM DISK = ”’ 
               + mf.physical_device_name + ”’ WITH NORECOVERY ,NOUNLOAD,  REPLACE,  STATS = 10′               
        WHEN B.type = ‘L’ THEN ‘RESTORE LOG AdventureWorks_teste FROM DISK = ”’ 
               + mf.physical_device_name + ”’ WITH NORECOVERY’
    END AS Instrucao,
    DENSE_RANK() OVER (PARTITION BY B.TYPE ORDER BY B.Backup_Set_id desc) AS Ranking,
    @Move AS WithMove   
FROM    [SRVBD\PRODUCTION].msdb.dbo.backupset b,
        [SRVBD\PRODUCTION].msdb.dbo.backupmediafamily mf
WHERE   b.media_set_id = mf.media_set_id
        AND b.database_name = @nome_db
        AND b.backup_set_id >= @backup_set_id_inicio AND b.backup_set_id < @backup_set_id_fim
        AND b.type in (‘L’, ‘D’, ‘I’)
UNION
SELECT 999999999 AS backup_set_id, ‘Z’, ‘RESTORE DATABASE AdventureWorks_teste WITH RECOVERY’, 1, 0
),
–restore automático with move
Arquivos AS (
    SELECT
        *,
        @backup_set_id_inicio as  Backup_set_id,
        ‘D’ AS [Type],
        1 AS WithMove
    FROM (
    SELECT ‘,MOVE ‘ +
           ”” + logical_name + ”” + ‘ TO ‘ + ”’I:\Data\’ + RIGHT(physical_name, CHARINDEX(‘\’,REVERSE(physical_name)) – 1) + ””
    FROM    [SRVBD\PRODUCTION].msdb.dbo.backupset b,
            [SRVBD\PRODUCTION].msdb.dbo.backupmediafamily mf,
            [SRVBD\PRODUCTION].msdb.dbo.backupfile bf
    WHERE   b.media_set_id = mf.media_set_id AND
            b.backup_set_id = bf.backup_set_id
            AND b.database_name = @nome_db
            AND b.backup_set_id = @backup_set_id_inicio   
    FOR XML PATH(”)) X(Caminho)
)
–montagem das informações
SELECT
    Instrucao + ISNULL(A.Caminho, ”)
FROM Backups AS B
left outer JOIN Arquivos AS A
ON B.backup_set_id = A.backup_set_id
    AND B.WithMove = A.WithMove
    AND B.type = A.Type
WHERE (B.type IN (‘D’, ‘I’, ‘Z’) AND Ranking = 1)
    OR (B.type = ‘L’ and B.backup_set_id > (SELECT MAX(C.backup_set_id) FROM Backups AS C WHERE C.TYPE IN (‘I’, ‘D’) AND Ranking = 1))
ORDER BY B.type, B.Ranking DESC

GO

Referência: http://gallery.technet.microsoft.com/scriptcenter/Script-Restore-Automtico-faf3aca9

Anúncios