SQL SERVER – How to Roll Back SQL Server Database Changes
- by Pinal Dave
In a perfect scenario, no unexpected and unplanned changes occur. There are no unpleasant surprises, no inadvertent changes. However, even with all precautions and testing, there is sometimes a need to revert a structure or data change.
One of the methods that can be used in this situation is to use an older database backup that has the records or database object structure you want to revert to. For this method, you have to have the adequate full database backup and a tool that will help you with comparison and synchronization is preferred.
In this article, we will focus on another method: rolling back the changes.
This can be done by using:
An option in SQL Server Management Studio
T-SQL, or
ApexSQL Log
The first two solutions have been described in this article
The disadvantages of these methods are that you have to know when exactly the change you want to revert happened and that all transactions on the database executed in a specific time range are rolled back – the ones you want to undo and the ones you don’t.
How to easily roll back SQL Server database changes using ApexSQL Log?
The biggest challenge is to roll back just specific changes, not all changes that happened in a specific time range.
While SQL Server Management Studio option and T-SQL read and roll forward all transactions in the transaction log files, I will show you a solution that finds and scripts only the specific changes that match your criteria. Therefore, you don’t need to worry about all other database changes that you don’t want to roll back.
ApexSQL Log is a SQL Server disaster recovery tool that reads transaction logs and provides a wide range of filters that enable you to easily rollback only specific data changes.
First, connect to the online database where you want to roll back the changes.
Once you select the database, ApexSQL Log will show its recovery model. Note that changes can be rolled back even for a database in the Simple recovery model, when no database and transaction log backups are available. However, ApexSQL Log achieves best results when the database is in the Full recovery model and you have a chain of subsequent transaction log backups, back to the moment when the change occurred.
In this example, we will use only the online transaction log.
In the next step, use filters to read only the transactions that happened in a specific time range.
To remove noise, it’s recommended to use as many filters as possible. Besides filtering by the time of the transaction, ApexSQL Log can filter by the operation type:
Table name:
As well as transaction state (committed, aborted, running, and unknown), name of the user who committed the change, specific field values, server process IDs, and transaction description.
You can select only the tables affected by the changes you want to roll back. However, if you’re not certain which tables were affected, you can leave them all selected and once the results are shown in the main grid, analyze them to find the ones you to roll back.
When you set the filters, you can select how to present the results. ApexSQL Log can automatically create undo or redo scripts, export the transactions into an XML, HTML, CSV, SQL, or SQL Bulk file, and create a batch file that you can use for unattended transaction log reading.
In this example, I will open the results in the grid, as I want to analyze them before rolling back the transactions.
The results contain information about the transaction, as well as who and when made it.
For UPDATEs, ApexSQL Log shows both old and new values, so you can easily see what has happened.
To create an UNDO script that rolls back the changes, select the transactions you want to roll back and click Create undo script in the menu.
For the DELETE statement selected in the screenshot above, the undo script is:
INSERT INTO [Sales].[PersonCreditCard] ([BusinessEntityID], [CreditCardID], [ModifiedDate])
VALUES (297, 8010, '20050901 00:00:00.000')
When it comes to rolling back database changes, ApexSQL Log has a big advantage, as it rolls back only specific transactions, while leaving all other transactions that occurred at the same time range intact. That makes ApexSQL Log a good solution for rolling back inadvertent data and schema changes on your SQL Server databases.
Reference: Pinal Dave (http://blog.sqlauthority.com)Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL Tagged: ApexSQL
