ROLLBACK A DATABASE SERVER IN 2008 SQL IN FULL RECOVERY MODEL

If you have a db in full recovery model that needs point in time recovery but the transaction log has not yet been backed up.  Can you rollback a database without using backup files.

for instance
transaction logs backup up once a day at 1 am.    the next day someone makes a big mistake at 3 pm.  can I roll back the database to 2:55 pm without using backup files?  or do I have to wait until the transaction log backs up at midnight.
or do I have to manually backup the transaction log now. and recovery using the backups.

Whats the best way to rollback a database in this instance
thank you!
jamesmetcalf74Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
You have to manually back up the tran log, then recover the db.

If you are absolutely sure you want to recover that specific db, you can do a tail-log backup (that is, specify "WITH NORECOVERY", which will make the main db unusable until you recover it).
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
You can't rollback committed transactions. The only way it's really the restore option and for that as ScottPletcher mentioned you need to perform a tail-log backup so the period that you want to recover will be in the backup plan.

Anyway you should review you backup plan. A single transaction log backup by day doesn't seems a good idea. You should perform transaction log backups more often and at least a daily differential backup.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.