Point in Time Backup (impossible for some points?)

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • mcaglar@cs.ucf.edu
    #1

    Point in Time Backup (impossible for some points?)

    Hello,

    I am using SQL Server 2000 with SP4. I have a database with two full
    backups at 4:00 PM and 5:00 PM and a transactional log backup at 5:30
    PM. Is there a possible way to do a point in time restore to 4:30 PM,
    that is between two full backups?

    When I try to use the transactional log backup that is taken at 5:30, I
    can never specify a time before 5:00 PM. Is the transaction log
    truncated at each full backup? If so, even if you take transactional
    log backup every ten minutes, and full backups every once in a while,
    there will be some point in time which cannot be recovered to, namely
    the time between a transactional log backup and a full backup. Take a
    log backup at 4:50, and full backup at 5:00 and you can never recover
    to 4:55, can you?

    Any insight on the topic will be appreciated,

    Regards,

    M. Baris Caglar

    Tags: None
    • Erland Sommarskog
      #2
      Re: Point in Time Backup (impossible for some points?)

      mcaglar@cs.ucf. edu (mcaglar@cs.ucf .edu) writes:
      I am using SQL Server 2000 with SP4. I have a database with two full
      backups at 4:00 PM and 5:00 PM and a transactional log backup at 5:30
      PM. Is there a possible way to do a point in time restore to 4:30 PM,
      that is between two full backups?
      Yes, restore the backup from 16:00 with NORECOVERY and then the
      transaction log with the STOPAT option. Check the exact syntax in
      Books Online.

      This presumes that the log chain was never broken. That is the most
      previous T-log backup of any kind must have been taken before 16:00.
      SQL Server will tell you if this is the ase.
      When I try to use the transactional log backup that is taken at 5:30, I
      can never specify a time before 5:00 PM.
      Don't really know what you mean, but if you are using some GUI, I
      don't really know what happens. I prefer to use T-SQL commands.
      Is the transaction log truncated at each full backup?
      No. BACKUP DATABASE backs up the database, and all it does with the
      log is to write a log record.

      But if the database was taken as part of a job, that job may include a
      backup of the transaction log as well. At worst, it includs a backup
      with any of the options TRUNCATE_ONLY of NO_LOG which just throws
      the logs away, without saving them anywhere.

      There are tables in msdb where you can see at which points various sorts
      of backups were taken. I don't use these tables very often myself, so
      I can't give you an exact query to run.



      --
      Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

      Books Online for SQL Server 2005 at
      http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

      Books Online for SQL Server 2000 at
      http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

        Comment

        • mcaglar@cs.ucf.edu
          #3
          Re: Point in Time Backup (impossible for some points?)

          I had find the exact same solution at a different thread in this group
          and it worked, but thank you for your response. Interestingly,
          Enterprise manager does not allow to perform such action. I wonder if
          this was a bug or a design issue. Does anyone know if this peoblem is
          fixed on SQL Server 2005?

          Baris

          Erland Sommarskog wrote:
          mcaglar@cs.ucf. edu (mcaglar@cs.ucf .edu) writes:
          I am using SQL Server 2000 with SP4. I have a database with two full
          backups at 4:00 PM and 5:00 PM and a transactional log backup at 5:30
          PM. Is there a possible way to do a point in time restore to 4:30 PM,
          that is between two full backups?
          >
          Yes, restore the backup from 16:00 with NORECOVERY and then the
          transaction log with the STOPAT option. Check the exact syntax in
          Books Online.
          >
          This presumes that the log chain was never broken. That is the most
          previous T-log backup of any kind must have been taken before 16:00.
          SQL Server will tell you if this is the ase.
          >
          When I try to use the transactional log backup that is taken at 5:30, I
          can never specify a time before 5:00 PM.
          >
          Don't really know what you mean, but if you are using some GUI, I
          don't really know what happens. I prefer to use T-SQL commands.
          >
          Is the transaction log truncated at each full backup?
          >
          No. BACKUP DATABASE backs up the database, and all it does with the
          log is to write a log record.
          >
          But if the database was taken as part of a job, that job may include a
          backup of the transaction log as well. At worst, it includs a backup
          with any of the options TRUNCATE_ONLY of NO_LOG which just throws
          the logs away, without saving them anywhere.
          >
          There are tables in msdb where you can see at which points various sorts
          of backups were taken. I don't use these tables very often myself, so
          I can't give you an exact query to run.
          >
          >
          >
          --
          Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
          >
          Books Online for SQL Server 2005 at
          http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

          Books Online for SQL Server 2000 at
          http://www.microsoft.com/sql/prodinf...ons/books.mspx

            Comment

            • Erland Sommarskog
              #4
              Re: Point in Time Backup (impossible for some points?)

              mcaglar@cs.ucf. edu (mcaglar@cs.ucf .edu) writes:
              I had find the exact same solution at a different thread in this group
              and it worked, but thank you for your response. Interestingly,
              Enterprise manager does not allow to perform such action. I wonder if
              this was a bug or a design issue. Does anyone know if this peoblem is
              fixed on SQL Server 2005?
              Enterprise Manager is not included in SQL 2005, neither is Query Analyzer.
              Both tools have been superceded by SQL Server Management Studio. Whether
              the GUI dialogs in Mgmt Studio would make this operation available to you
              I don't know. In any case, the GUI are just wrappers on the T-SQL commands,
              and you can always use T-SQL when the GUI does not expose a certain piece
              of functionality.

              --
              Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

              Books Online for SQL Server 2005 at
              http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

              Books Online for SQL Server 2000 at
              http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                Comment

                Previous template Next
                Working...