RESTORE LOG ... WITH RECOVERY, STOPAT option allows you to restore database backup to a point in time. This option gives you the ability to restore a database backup file prior to an event that occurred.
Each of these transactions has a LSN (log sequence number) along with a timestamp, so when you restoring the transaction log you have the ability to tell SQL Server where to stop reading transactions that need to be restored.
For expample my database got currupted at -- 13:58.
I have full backup at 13:00 and Transaction log backup at 14:06
I want to restore the log at this point 'Nov 07, 2012 01:57:00 PM'
Use AdventureWorks
go
-- Full Backup has taken 13:00
Select * from Emp where EmployeeID =5;
--Title =Tool Designer
--13:46
UPDATE Emp SET Title ='Tool Designer Engineer' where EmployeeID =5;
GO
Select * from Emp where EmployeeID =6;
--Title =Marketing Manager
-- 13:52
UPDATE Emp SET Title ='Assit Marketing Manager' where EmployeeID =6;
GO
-- Database has been corrupted
-- 13:58
Select * from Emp where EmployeeID =7;
--Title =Production Supervisor - WC60
-- 14:01
UPDATE Emp SET Title ='Production Supervisor' where EmployeeID =7 ;
GO
-- 14:06 Transaction Backup completed
-- Restore Full Database @ 13:00 with NORECOVERY
use master
GO
RESTORE DATABASE AdventureWorks FROM
DISK = 'E:\MSSQL\Backup\FULL\AdventureWorks_7_Nov2012_13h0m_Full_1.BAK',
DISK = 'E:\MSSQL\Backup\FULL\AdventureWorks_7_Nov2012_13h0m_Full_2.BAK',
DISK = 'E:\MSSQL\Backup\FULL\AdventureWorks_7_Nov2012_13h0m_Full_3.BAK'
WITH NORECOVERY , STATS = 5
GO
-- Point in time restore
-- Restoring now Transaction log backup WITH RECOVERY, STOPAT = 'Nov 07, 2012 01:57:00 PM'
RESTORE LOG AdventureWorks
FROM DISK ='E:\MSSQL\Backup\TRAN\AdventureWorks_7_Nov2012_14h6m_TRAN_1.BAK'
WITH RECOVERY, STOPAT = 'Nov 07, 2012 01:57:00 PM' , STATS = 5
GO
select EmployeeID,NationalIDNumber, ManagerID,Title
from Emp where EmployeeID IN(5,6,7)
