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.