Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    70

    Unanswered: Point in time recovery

    Dear All,
    How do we accomplish point in time recovery with SQL Server database.

    For example:
    My backup schedule is
    Monday - Complete database
    Tuesday - transaction log backup
    Wednesday-transaction log backup
    Thursday - transaction log backup
    Friday - Complete database
    Saturday - transaction log backup
    Sunday - transaction log backup

    For complete database backup, I use the below syntax:

    BACKUP DATABASE myDB
    TO DISK= @File1
    WITH DESCRIPTION = @Desc

    For transaction log backup, I use the below syntax:
    BACKUP LOG myDB
    TO DISK= @File1
    WITH DESCRIPTION = @Desc


    With this scenario, can I accomplish a point in time recovery? For example, if my database crashes on thursday night. How do i do complete recovery till that time?

    Pls guide...

    Regards,
    qA

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Easy to do with Enterprise Manager.
    Using SQL, this is straight from Books Online:
    Code:
    How to restore to a point in time (Transact-SQL)
    To restore to a point in time 
    
    Execute the RESTORE DATABASE statement using the NORECOVERY clause.
    
    
    Execute the RESTORE LOG statement to apply each transaction log backup, specifying: 
    The name of the database to which the transaction log will be applied.
    
    
    The backup device from where the transaction log backup will be restored.
    
    
    The RECOVERY and STOPAT clauses. If the transaction log backup does not contain the requested time (for example, if the time specified is beyond the end of the time covered by the transaction log), a warning is generated and the database remains unrecovered. 
    Examples
    This example restores a database to its state as of 10:00 A.M. on July 1, 1998, and illustrates a restore operation involving multiple logs and multiple backup devices.
    
    -- Restore the database backup.
    RESTORE DATABASE MyNwind
       FROM MyNwind_1, MyNwind_2
       WITH NORECOVERY
    GO
    RESTORE LOG MyNwind
       FROM MyNwind_log1
       WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
    GO
    RESTORE LOG MyNwind
       FROM MyNwind_log2
       WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
    It pays to read the manual....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •