• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

Point in time recovery...

Hey guys,

Just looking for some verification.  In order to do point in time recovery of a SQL Server database (or any database for the most part) I need to have a valid database backup and all the associated transaction log backups to the point in time that I want to recover too, correct?  

In other words if I wanted to restore my database to say 1pm this afternoon, I need to have a backup from last night (say 2am) and a backup of all the transaction logs going back to 1pm this afternoon.  I cannot restore the database via point in time with out those transaction log backups correct?

My boss is trying to tell me that I can do a point in time restore just by having last nights DB backup and also the current transaction log file that has been excepting transactions throughout the day with no transaction log backups at all.  I'm trying to tell him that that isn't possible?

High points on this because I'm trying to prove my boss, who never listens, wrong.

Any help is greatly apprecaited.


Pete J.
3 Solutions
Scott PletcherSenior DBACommented:
Yes, you *must* have a log backup.  

However, if the current db functions well enough after the problem to allow a log backup to be taken, that will usually work for recovery.  That is, backup the current log, restore the previous backup, apply that backup.  But that's a *VERY DANGEROUS* assumption -- basically all the stars must align for the current log to be of full use.
You cannot restore from a .ldf....only from a valid backup (typically .trn, but not always).
A simple test would be go to EM

Right click on db and say restore. Set the point in time recovery to a time. SQL server will automatically show you all the backups it needs to restore to do a point in time recovery.
PJan8724Author Commented:
Thanks for the help guys.  Got the information that I needed and the boss saw my point.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now