Restore transaction log point in time

This is an awesome feature where in one can restore transaction log point in time. A database can be restored to a specified using STOPAT option in restore command. Let’s look at it step by step.

Let’s first take a full backup of a AdventureWorks database.

Backup database AdventureWorks to disk='E:\Backup\AdventureWorks_full.bak' with init

I’ll do some modifications to table Person.Addresstype, so let’s first check whats in the table.

The below query does some modifications in Person.Addresstype table.

UPDATE Person.AddressType 
SET Name='Billing' WHERE AddressTypeID=1
GO
WAITFOR DELAY '00:01:00'
GO
Insert into Person.AddressType 
SELECT 'US Office',newid(),Getdate()
GO
WAITFOR DELAY '00:01:00'
GO
DELETE FROM Person.AddressType 
WHERE Name='Godown Office'
GO

Let’s take a transaction log backup using below command

Backup LOG AdventureWorks to disk='E:\Backup\AdventureWorks_Log_0230.bak' with init

Now, consider the fact that the delete operation after the insert one was mistakenly done and needs to be rolled back. The STOPAT option comes handy here. Let’s now recover our data by first restoring the full backup with NORECOVERY option.

Backup database AdventureWorks to disk='E:\Backup\AdventureWorks_full.bak' with init

Let’s now restore the T-Log with STOPAT 2014-09-29 02:27:00:170

RESTORE LOG AdventureWorks from disk='E:\Backup\AdventureWorks_Log_0230.bak' WITH RECOVERY,REPLACE,STOPAT='Sep 29, 2014 02:27:00.170 AM'

Let’s do a select on Person.Addresstype table again and check whether we have got our deleted value back or not.

Thus, the STOPAT option can be a savior in cases where a Jr. DBA/Developer deletes an entire table instead of a particular value.

 
Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook