Restaurar dados excluídos de uma tabela que ocorreu diariamente

Seria um processo árduo, mas você pode usar RESTORE WITH STANDBYpara uma instância diferente. Depois que cada log for restaurado, você poderá ler os dados no banco de dados. Não há garantia de que as linhas que você está procurando não foram adicionadas e excluídas no mesmo backup de log de transações.

RESTAURAR Declarações

STANDBY =standby_file_name Especifica um arquivo em espera que permite que os efeitos de recuperação sejam desfeitos. A opção STANDBY é permitida para restauração offline (incluindo restauração parcial). A opção não é permitida para restauração online. A tentativa de especificar a opção STANDBY para uma operação de restauração online faz com que a operação de restauração falhe. STANDBY também não é permitido quando uma atualização de banco de dados é necessária.

O arquivo de espera é usado para manter uma pré-imagem "copiar na gravação" para páginas modificadas durante a passagem de desfazer de um RESTORE WITH STANDBY. O arquivo em espera permite que um banco de dados seja ativado para acesso somente leitura entre restaurações de log de transações e pode ser usado com situações de servidor em espera quente ou situações de recuperação especiais nas quais é útil inspecionar o banco de dados entre restaurações de log.

Em uma nota lateral ( em referência à sua declaração sobre backups de log de transações por hora ), essa situação é uma das razões pelas quais eu gosto de fazer backup de logs de transações a cada minuto. Sim, realmente. para meus bancos de dados críticos.

Aqui está um exemplo de uso do WITH STANDBY.

/* Create our test database.  You may need to change the file paths for your environment. */
CREATE DATABASE [StandbyTest] ON  PRIMARY 
( NAME = N'StandbyTest', FILENAME = N'C:\StandbyTest\StandbyTest.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )

LOG ON 

( NAME = N'StandbyTest_log', FILENAME = N'C:\StandbyTest\StandbyTest_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [StandbyTest] SET RECOVERY FULL 
GO
USE [StandbyTest]
GO

/* Create some dummy data */
CREATE TABLE dbo.Customers (CustomerID INT, CustomerName VARCHAR(50))
GO
INSERT INTO dbo.Customers (CustomerID, CustomerName)

 VALUES (1, 'Bill Gates')

INSERT INTO dbo.Customers (CustomerID, CustomerName)

 VALUES (2, 'Steve Ballmer')

GO

/* Do a full backup of the database */
BACKUP DATABASE [StandbyTest] TO  DISK = N'C:\StandbyTest\StandbyTest.bak' 
WITH FORMAT, INIT,  
NAME = N'StandbyTest-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

/* Add some more dummy data */
INSERT INTO dbo.Customers (CustomerID, CustomerName)

 VALUES (3, 'Donald Farmer')


/* Do a transaction log backup */
BACKUP LOG [StandbyTest] TO  DISK = N'C:\StandbyTest\StandbyTest.trn' 
WITH FORMAT, INIT,  
NAME = N'StandbyTest-Transaction Log  Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

/* Pretend we're a developer */
DROP TABLE dbo.Customers
GO

/* DISASTER!  We need to go back in time! */
USE master
GO
RESTORE DATABASE [StandbyTest] 

 FROM  DISK = N'c:\StandbyTest\StandbyTest.bak' 
 WITH  FILE = 1,
 REPLACE,
 STANDBY = N'C:\StandbyTest\ROLLBACK_UNDO_StandbyTest.BAK'

GO

/* Test to see if we can read records */
SELECT * FROM StandbyTest.dbo.Customers
/* Notice that we only got 2 customers, because we haven't restored the t-log yet. */

RESTORE LOG [StandbyTest] 

 FROM  DISK = N'C:\StandbyTest\StandbyTest.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 10

GO

/* Test to see if we can read records */
SELECT * FROM StandbyTest.dbo.Customers

Pelo que parece, você não tem uma única vez desconhecida em que os dados foram excluídos, você tem várias vezes que os dados teriam sido excluídos.

Mas boas notícias! Você tem backups de log que cobrem o período de tempo em questão, então não é impossível. Se eu fosse confrontado com seus requisitos, eu faria o seguinte. É totalmente possível fazer o script desse processo, mas as etapas básicas são:

1. Decida em um período de tempo para o qual você deseja restaurar. A cada minuto, a cada 5 minutos, etc. Você pode ir menor, a cada segundo, por exemplo, mas isso tornará o resto doloroso.

2. Restaure o backup completo para um novo banco de dados (ou mesmo banco de dados em outro servidor).

3. Copie o conteúdo da sua tabela para uma tabela diferente em outro banco de dados, exporte, seja o que for.

4. Restaure o backup completo, + primeiro log com a parada na hora que você quiser.

5. GOTO Etapa 3 até que você tenha todos os dados desejados.

Pode haver ferramentas de terceiros que podem fazer isso para você também, eu sei que algumas são capazes de fazer restaurações no nível da tabela a partir de backups nativos. Você pode misturar essa estratégia com o RESTORE WITH STANDBY que Scott Hodgin mencionou também.

Oi eu criei o script para restaurar a cada 1 minuto e depois copie os dados para outra tabela em outro banco de dados.

create table #GARrestorefiles(
backupsetid int
,backupstartdate datetime
,backupfinishdate datetime
,databasename varchar(100)
,backupfile VARCHAR(100)
)
insert into #GARrestorefiles (backupsetid,backupstartdate,backupfinishdate,databasename,backupfile) 
SELECT bs.backup_set_id,bs.backup_start_date,(bs.backup_finish_date),bs.database_name,
bms.physical_device_name
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bms
ON bs.media_set_id = bms.media_set_id
INNER JOIN master.dbo.sysdatabases s
ON bs.database_name = s.name
WHERE
s.dbid IN (5) 
AND bs.type = 'L'
--and backup_set_id = 1113
and backup_finish_date > '2018-07-27 08:45:00.000' 
--and backup_finish_date < '2018-07-25 13:00:00.000'
group by bs.database_name,bs.backup_set_id,bms.physical_device_name,bs.backup_start_date,bs.backup_finish_date
order by bs.backup_finish_date asc


DECLARE @DBfileToRestore VARCHAR(100),@Backupfinishdate datetime, @backupLocation varchar(500)
--, @mdfLocation varchar(500)
--, @ldfLocation varchar(500)

DECLARE restorecursor CURSOR FOR
SELECT backupfile,backupfinishdate FROM #GARrestorefiles

OPEN restorecursor

FETCH NEXT FROM restorecursor
INTO @DBfileToRestore,@Backupfinishdate

WHILE @@FETCH_STATUS = 0
BEGIN
    --SET @mdfLocation = 'F:\Files\Restore_Files\' + @DBfileToRestore + '.mdf'
    --SET @ldfLocation = 'F:\Files\Restore_Files\' + @DBfileToRestore + '.ldf'
--**********************************************************************************

--DECLARE @cmd nvarchar(4000)
DECLARE @srcbkpLocation nvarchar(4000)
DECLARE @destbkpLocation nvarchar(4000)
DECLARE @bkpFileName nvarchar(4000)
DECLARE @bkpFileName1 nvarchar(4000)
declare @dbname nvarchar(40)
DECLARE @date datetime;
DECLARE @Start_time datetime = DATEADD(minute,-5,@Backupfinishdate);
DECLARE @increase INT = 01;
DECLARE @STOPAT_time DATETIME = DATEADD(minute, @increase, @Start_time);
declare @backup_finish_date datetime
DECLARE @backupsetid int = 1878;

SET @backupLocation = @DBfileToRestore
SET @backup_finish_date = @Backupfinishdate
while(@STOPAT_time <= DATEADD(minute,01,@backup_finish_date))
    BEGIN
    PRINT @STOPAT_time
        RESTORE LOG AICS_QUERY_DEMO
        FROM DISK = @backupLocation
        WITH FILE = 1
        ,NOUNLOAD
        ,STATS = 10
        ,STANDBY = N'F:\SQL_Backup\AICS_Query\ROLLBACK_AICS_QUERY_DEMO.BAK'
        --,stopat = '2018-07-26 09:10:00' --10 minute interval
        ,stopat = @STOPAT_time
        SET @STOPAT_time = DATEADD(minute, @increase, @STOPAT_time);
    END
    INSERT INTO [RestoreDB].[dbo].[EmployeeDetail](Id,Name,Age,Location) 
    select Id,Name,Age,Location from [AICS_QUERY_DEMO].[dbo].[EmployeeDetail]
--Created a unique index on Id column with ignore duplicate ON to avoid duplicate entry.
--*******************************************************************************************
    FETCH NEXT FROM restorecursor
    INTO @DBfileToRestore,@Backupfinishdate
END
CLOSE restorecursor
DEALLOCATE restorecursor

Eu uso um cursor para percorrer diferentes arquivos de log de transações e, em seguida, um loop while para parar a cada 1 minuto e, em seguida, restaurar uma tabela.