Point in time recovery...

Posted on 2006-05-11
Last Modified: 2008-02-01
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.
Question by:PJan8724
    LVL 20

    Accepted Solution

    LVL 68

    Assisted Solution

    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.
    LVL 21

    Assisted Solution

    You cannot restore from a .ldf....only from a valid backup (typically .trn, but not always).
    LVL 20

    Expert Comment

    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.
    LVL 1

    Author Comment

    Thanks for the help guys.  Got the information that I needed and the boss saw my point.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now