Reverting your SQL Server database back to a specific point in time (STOPAT)

Introduction

There are certain circumstances in which you may need to roll back your database to a specific point in time. There may be various reasons why this could be necessary but it is usually related to the execution of accidental or malicious DML or DDL statements. For example:

• Someone executed an update without a WHERE clause
• Someone accidently dropped a table
• Your attempted database upgrade failed, and left the database in an unknown state

SQL Server provides two mechanisms for you to recover from these events:

• SQL Server Database snapshots
• Point-in-time restore

SQL Server Database snapshots

If you know that you are going to perform a task which may damage your data, it would be prudent to take a database snapshot before you do it

Database snapshots are quick and easy to create, and very easy to roll back. Of course if you did not anticipate the damaging event, or you do not use database snapshots as part of your regular data protection strategy this may not be an option for you, and you will be forced to use the point-in-time restore method. You can read more about this in the Using SQL Server database snapshots to protect yourself against accidental data modification online article

Point-in time restore

It’s important to know, that getting back to a specific point in time is not always an option, since it is completely dependent on 2 things:

1. The database recovery model and
2. Your log chain

SQL Server Recovery models

SQL Server offer 3 recovery models

• Simple
• Bulk-logged
• Full

Choosing your recovery model is a pivotal decision, because it essentially defines how much data you are prepared to lose in case of a disaster

In the case of a simple recovery model, you are essentially agreeing that it is ok to lose all changes made to your database after the last full backup. The upside of this recovery model is of course that it takes very little administration. You don’t have to worry about anything like a log chain or tons of log backups, or even when the log will be truncated. Obviously this will never be good enough for mission critical databases, but it definitely has its place and its uses

The purpose of the bulk logged recovery model is to allow you perform bulk operations without writing each transaction to the transaction log, and as such improve the performance of your bulk operation. The downside to this is of course that not each transaction is recorded in the log, and as such it does not allow you to do a point in time restore

In order to be able to restore to a specific point in time, you need to have your database set to use the full recovery model. This means that every event which takes place in the database gets written to the log, which is why it then makes it possible for you to restore up to a specific point. But just having the database set to the full recovery model is not enough. Unless you have a complete log chain, you will still not be able to restore your database to the point in time you require

The log chain

The log chain starts when you create a full backup of a database which is in full recovery model. This means that between this full backup and the next full back up each operation which occurred in the database will be recorded in either the full backup, a differential backup or a log backup

This ensures that when you need to restore your database up to a specific time or a specific transaction, all the required log entries will be there to make this possible

As long as the log chain is not broken, by for instance changing the recovery model to a different recovery model and back again or rolling back to a database snapshot, you will be able to recover your database to a specific point in time. Incidentally, taking another full back up in between log backups does not break the log chain

If your log chain gets broken, you can restart it by simply creating a new full or differential backup

Performing a point-in-time restore with SQL Server

SQL Server allows you to perform the point in time restore by using either T-SQL or SSMS

To be able to perform a point in time restore you need to restore the full back up which ended prior to the point you would like to restore to , and then restore all the log backups including the one which contains the point you want to get to

Using SQL Server Management Studio

To use SQL Server Management studio, you can follow the following procedure:

1. Right click on the database you wish to revert back to a point in time
2. Select Tasks/Restore/Database
   
   SSMS will automatically check all available backups starting from the latest full backup
3. On the restore database dialog select the Timeline option
4. Enter the exact point in time you want the database to be restored to, by either dragging the slider to the desired point, or by selecting it using the date and time picker
   
   Note that in SQL Server 2012, it will automatically create a tail log backup before starting the restore
5. Click Ok
6. Click Ok again

This will start the restore

Using Transact SQL

Any point in time restore requires a full log chain, regardless of the method used to perform the restore. This means you need to restore that last full backup and all log backups including the one which contains the point you would like to restore to

The point in time is always restored from a log backup. Which means you need to use the RESTORE LOG statement, with the STOP AT clause to restore the database to a point in time. The operation it would be restored to, is the last transaction which had been successfully committed prior to the point specified

Here is a script example

1. USE master
   
   RESTORE DATABASE AdventureWorks2012
   FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\
   MSSQL\Backup\AW2012.bak'
   WITH FILE = 3
    ,REPLACE
    ,NORECOVERY;
   
   In the example above we will restore the full backup from the file ‘D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\
   MSSQL\Backup\AW2012.bak’. Since this file contains multiple backups, we need to specify which FILE to use
   You can identify the file number by looking at the Position column when executing this command:

   RESTORE HEADERONLY
   FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\
   MSSQL\Backup\AW2012.bak'
   
   The REPLACE option indicates that it will over-write the existing the database. The same way it would have been over-written following the steps in SSMS
2. Next restore each log, specifying the date and time to STOP AT. This needs to be specified in each RESTORE LOG statement

   RESTORE LOG AdventureWorks2012
   FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\
   MSSQL\Backup\AW2012.bak'
   WITH FILE = 4 ,NORECOVERY ,STOPAT = '2013-09-28 10:16:28.873’;
   
   RESTORE LOG AdventureWorks2012
   FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\
   MSSQL\Backup\AW2012.bak'
   WITH FILE = 5 ,NORECOVERY ,STOPAT = '2013-09-28 10:16:28.873’;
   
3. And finally recover the database

   RESTORE DATABASE AdventureWorks2012 WITH RECOVERY;
   

Restoring to a point in time with SQL Server, could take a significant amount of time, depending on the size of your database. SQL Server is basically doing a complete restore from the last full backup, and all the log backups up to the point to which you want to get to

Aside from the time it takes to perform the restore, another disadvantage is that the database is not accessible during this period

If you have a very large database, and you really only want to undo the last couple of transactions, it may be easier to use a tool like Apex SQL Log to identify what those last few transactions were and undo them

Using ApexSQL Log to revert back to a specific point in time

ApexSQL Log is a SQL Server transaction log reader, which can be used to identify the last transactions performed by reading the SQL Server online transaction log. ApexSQL Log can then also create an undo script to help you to undo these changes

Here is how to do it:

1. Open ApexSQL Log and start a new session pointing to the database that you wish to revert back to a specific point in time
   
2. Next you need to add all backups or log backups which contain the data that you wish to roll back. This means: All the data which occurred after the unwanted event
   
3. Filter the log transactions based on the time. In this case, since you only want to undo transactions which occurred after a specific time, you only need to specify the “from date”, which is point you want the database to be reverted back to
   
4. Once you selected the date you want roll back to, you need to tell ApexSQL Log what to do with the results. For the sake of this demo we will open the results in grid view. This will allow us to see and choose which transactions we want to roll back. This is especially handy when you are not 100% certain to which point you need to roll back
5. Select the Open results in grid view option
   
6. Review the transactions displayed in the grid as a final verification. You can indicate which operations to include by either highlighting or selecting them. In this case, we want to roll back everything after the date we selected
7. Right click and Choose the Select All option on the context menu
   
8. Once all operations are selected, Right click, and Select the Create Undo Script option
   
   The script will be created for you in a SQL Server script editor, which will allow you to execute the script immediately or to save the script and execute it from SSMS later
   

There are a couple advantages to use this approach

1. The database can remain online and accessible while you are undoing operations
2. ApexSQL Log has built in logic to eliminate redundant operations. Each operation is not directly applied to the database, but rather processed and only the final undo script it applied
3. Undoing a couple operations this way, is much faster than having to restore the entire database

Today, when time is so expensive, it is important to identify what needs to be done, and then use the best tool to do it. If half of your database has gone corrupt, it would be the best to do a complete restore from a backup. If you have identified a couple of operations which need to be undone, or you need to undo only a day or two, ApexSQL Log is definitely the way to go, saving you time, money and downtime

----------------------------------------------------------------------------------------------------------------

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' whereEmployeeID =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)