How to perform SQL Roll back from transaction logs

sql-server-2008-r2 rollback transaction-log
torres picture torres · Jun 5, 2013 · Viewed 22.9k times · Source

I have transaction log file that goes back 6 months. I need to roll back everything that happened after 5/20/2013 from a database. Can anyone please enlighten me on how to do this?

Answer

Milena Petrovic picture Milena Petrovic · Aug 2, 2013

First of all, copy the database MDF and LDF files. Better safe than sorry

The database can be restored to a point in time in SQL Server 2008R2, also. There's no need to create a transaction log backup first, it'll be done automatically by SQL Server. You can find more about the log-tail backup here: Tail-Log Backups

  1. Select to restore the database in the database context menu enter image description here

  2. Leave Database as Source. Click Timeline

enter image description here

  1. Select Specific date and time. If you drag the time pointer, you'll able to see how long back your transaction log goes. Note that bright green shows that the transactions have never been backed up

enter image description here

After all is done, schedule transaction log backups. There's no point to have a database in Full recovery model and never backup the online transaction log

Related questions

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated

I have many users on my web site (20000-60000 per day), which is a download site for mobile files. I have remote access to my server (windows server 2008-R2). I've received "Server is unavailable" errors before, but am now seeing …

Read More
Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build). Sometimes when I run my build I get this error: ALTER DATABASE failed because a lock could not be placed on …

Read More
Select SQL Server database size

how can i query my sql server to only get the size of database? I used this : use "MY_DB" exec sp_spaceused I got this : database_name database_size unallocated space My_DB 17899.13 MB 5309.39 MB It returns me several …

Read More