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

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 to 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 overwrite 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 or ApexSQL Recover 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.

ApexSQL Recover is a SQL Server database recovery tool which reads information from the ldf and mdf files and allows recovery from drop, truncate and delete operations, operation rollback, extraction directly from backup and more.

Both these tools share the functionality to read transaction log files and revert back to a specific point in time

Here is how to do it:

1. Open the application and start a new session pointing to the database that you wish to revert back to a specific point in time and click Next button

   

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. For the sake of this demo we will choose to 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

   

4. 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

   

5. 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

6. Select all by checking the white box next to operation in the grid

   

7. Once all operations are selected, select the Undo option in the main ribbon

   

   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 or another similar tool 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 and ApexSQL Recover have 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 or ApexSQL Recover are definitely the way to go, saving you time, money and downtime.

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.