How To Roll Back Data Operations In Ssis Package

Introduction

There are sure circumstances in which you may need to curl back your database to a specific betoken in time. At that place may be various reasons why this could exist necessary but it is unremarkably related to the execution of adventitious or malicious DML or DDL statements. For case:

Someone executed an update without a WHERE clause
Someone accidently dropped a tabular array
Your attempted database upgrade failed, and left the database in an unknown land

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

SQL Server Database snapshots
Signal-in-time restore

SQL Server Database snapshots

If you know that y'all are going to perform a task which may damage your data, information technology would be prudent to have a database snapshot before y'all exercise it.

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

Betoken-in time restore

It'south of import to know, that getting back to a specific point in time is not always an option, since it is completely dependent on 2 things:

The database recovery model and
Your log chain

SQL Server Recovery models

SQL Server offer iii recovery models

Uncomplicated
Bulk-logged
Total

Choosing your recovery model is a pivotal conclusion, because it essentially defines how much information you lot are prepared to lose in example of a disaster.

In the instance of a simple recovery model, y'all are essentially agreeing that it is ok to lose all changes fabricated to your database after the terminal total backup. The upside of this recovery model is of grade that it takes very little administration. You don't take to worry near anything like a log chain or tons of log backups, or even when the log volition be truncated. Obviously, this will never be good enough for mission critical databases, only it definitely has its place and its uses.

The purpose of the bulk logged recovery model is to permit you lot to perform majority operations without writing each transaction to the transaction log, and as such improve the functioning of your bulk performance. The downside to this is of course that non each transaction is recorded in the log, and every bit such it does not allow you to do a signal in fourth dimension restore.

In guild to exist able to restore to a specific indicate in fourth dimension, you lot need to have your database ready to apply the total 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 upwardly to a specific signal. Merely merely having the database set to the total recovery model is not enough. Unless you have a complete log chain, you will still non exist 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 total back up each functioning which occurred in the database will be recorded in either the total backup, a differential backup or a log backup.

This ensures that when you demand to restore your database upward to a specific time or a specific transaction, all the required log entries will exist there to make this possible.

Equally long equally the log concatenation is not broken, by for instance irresolute 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 betoken in time. Incidentally, taking another full support in betwixt log backups does non break the log chain.

If your log chain gets broken, y'all can restart it past simply creating a new full or differential backup.

Performing a point-in-fourth dimension restore with SQL Server

SQL Server allows y'all to perform the bespeak 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 concluded prior to the point y'all would similar to restore to, and then restore all the log backups including the one which contains the point you desire to go to.

Using SQL Server Direction Studio

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

Right click on the database you wish to revert back to a point in fourth dimension
Select Tasks/Restore/Database

SSMS will automatically check all available backups starting from the latest full fill-in
On the restore database dialog select the Timeline option
Enter the exact indicate in time you want the database to be restored to, by either dragging the slider to the desired point, or past selecting it using the date and fourth dimension picker

Note that in SQL Server 2012, it volition automatically create a tail log backup before starting the restore.
Click Ok
Click Ok once 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 ways you need to restore that terminal full backup and all log backups including the one which contains the point yous would like to restore to.

The betoken in time is always restored from a log backup. Which means y'all demand to utilise the RESTORE LOG statement, with the STOP AT clause to restore the database to a point in fourth dimension. The functioning it would be restored to, is the final transaction which had been successfully committed prior to the signal specified.

Here is a script example

                Utilise                master                RESTORE                DATABASE                AdventureWorks2012                FROM                Deejay                =                'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\ MSSQL\Backup\AW2012.bak'                WITH                FILE                =                iii                ,                Supersede                ,NORECOVERY;

In the example above we will restore the full backup from the file 'D:\Plan Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\
MSSQL\Backup\AW2012.bak'. Since this file contains multiple backups, we need to specify which FILE to use

Yous can place the file number past looking at the Position column when executing this control:

                RESTORE                HEADERONLY                FROM                Deejay                =                'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\ MSSQL\Backup\AW2012.bak'

The Supercede choice indicates that it volition overwrite the existing the database. The same manner it would have been over-written post-obit the steps in SSMS

Next restore each log, specifying the date and time to STOP AT. This needs to be specified in each RESTORE LOG argument

                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 ten:16:28.873';                RESTORE                LOG                AdventureWorks2012                FROM                DISK                =                'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\ MSSQL\Fill-in\AW2012.bak'                WITH                FILE                =                5                ,NORECOVERY                ,STOPAT                =                '2013-09-28 x:16:28.873';

And finally recover the database

                RESTORE                DATABASE                AdventureWorks2012                WITH                RECOVERY;

Restoring to a point in time with SQL Server, could have a significant amount of time, depending on the size of your database. SQL Server is basically doing a consummate restore from the last total 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, some other disadvantage is that the database is not accessible during this menstruum.

If yous 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 Noon 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 bespeak in fourth dimension

ApexSQL Log is a SQL Server transaction log reader, which tin be used to identify the terminal transactions performed by reading the SQL Server online transaction log. ApexSQL Log can then as well 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 dorsum to a specific point in fourth dimension

Hither is how to do it:

Open up the awarding and outset a new session pointing to the database that yous wish to revert back to a specific bespeak in fourth dimension and click Next push
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
For the sake of this demo we volition choose to Open the results in grid view. This will allow us to come across and choose which transactions nosotros want to scroll dorsum. This is especially handy when y'all are not 100% certain to which point you need to roll dorsum
Filter the log transactions based on the time. In this case, since you only want to undo transactions which occurred after a specific fourth dimension, you only need to specify the "from date", which is signal y'all want the database to exist reverted dorsum to
Review the transactions displayed in the grid as a final verification. Y'all can signal which operations to include past either highlighting or selecting them. In this case, we desire to roll back everything subsequently the date we selected
Select all past checking the white box next to operation in the grid
Once all operations are selected, select the Undo option in the primary 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 some other similar tool subsequently

There are a couple advantages to use this approach

The database can remain online and accessible while yous are undoing operations
ApexSQL Log and ApexSQL Recover have built in logic to eliminate redundant operations. Each functioning is not directly practical to the database, merely rather processed and only the final undo script information technology applied
Undoing a couple operations this way, is much faster than having to restore the entire database

Today, when fourth dimension is and then expensive, it is of import to identify what needs to be washed, and and then utilize the best tool to practice it. If one-half of your database has gone corrupt, information technology would be the best to do a complete restore from a fill-in. If you have identified a couple of operations which demand to be undone, or you need to undo only a mean solar day or ii, 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.

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

November 20, 2013