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.

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

1_restore transaction log point in time

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

2_restore transaction log point in time

Let’s take a transaction log backup using below command

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.

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

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

3_restore transaction log point in time

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



Ahmad Osama

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

Follow me on TwitterFollow me on FaceBook