Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to restore the rollback changes to a database? One of my client has done wrong updates on database so most of the tables have been updated with wrong data. That database is having with Replication, So can anyone suggest me without or less downtime can i restore the database with last 2days of data. Thanks in advance

share|improve this question

closed as unclear what you're asking by Mark Storey-Smith, Paul White, Phil, Jon Seigel, RolandoMySQLDBA May 27 at 19:51

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

2  
Do you have backups of the database and what is the recovery model set to for the database (select recovery_model_desc from sys.databases where name = 'YourDatabaseName') –  Scott Chamberlain Apr 1 at 17:52
    
"Restore the rollback changes" - meaning you committed an update, then rolled it back (undo), and want to roll it forward again (redo)? –  Aaron Bertrand Apr 1 at 19:28

3 Answers 3

If your database is in FULL or BULK-LOGGED recovery model, and you have LOG backups, and your backup chain is intact (no TRUNCATE of the log, converting to and from SIMPLE recovery model, etc.), then the simplest answer is PITR, i.e. Point In Time Recovery, i.e. the STOPAT argument for RESTORE LOG.

See the Technet articles How to: Restore to a Point in Time (SQL Server Management Studio) and How to: Restore to a Point in Time (Transact-SQL), but a simple example from there is:

RESTORE DATABASE AdventureWorks2008R2
   FROM AdventureWorks2008R2Backups
   WITH FILE=3, NORECOVERY;

RESTORE LOG AdventureWorks2008R2
   FROM AdventureWorks2008R2Backups
   WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';

RESTORE LOG AdventureWorks2008R2
   FROM AdventureWorks2008R2Backups
   WITH FILE=5, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
RESTORE DATABASE AdventureWorks2008R2 WITH RECOVERY; 

Watch out for regional settings on the dates, of course. It's just like any Full [+ DIFF] + LOG recovert, except for the addition of the STOPAT parameter.

share|improve this answer
    
can we do this without effecting Replication –  DBA Varma Apr 2 at 9:13
    
This is independent of replication; this is merely using BACKUP DATABASE (and BACKUP LOG for all FULL and BULK-LOGGED databases) to take backups, like every SQL Server instance should be doing. –  Anti-weakpasswords Apr 3 at 5:50

It is possible to rollback wrong updates without any downtime using ApexSQL Log. For maximal UPDATE reconstruction, the database should be in the Full recovery model, and a full chain of transaction logs is needed.

A full chain of transaction logs starts with a full database backup and is followed by all subsequent transaction log backups up to a point of the rollback.

enter image description here

How to recover SQL Server data from accidental UPDATE and DELETE operations

How to recover SQL Server data from accidental updates without backups

Disclaimer: I work for ApexSQL as a Support engineer

share|improve this answer

Is it possible to restore the rollback changes to a database?

No its not possible EASILY and without considerable downtime UNLESS --

A backup before doing updates is available. If not, third party tools like ApexSQLLog would be a better choice rather than going the native route.

If a backup before upadate is available, then you can restore it as a database_temp and do a data compare and updating that data backup. Highly recommend to use Redgate's SQL Data Compare. It just works flawless :-)

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.