point of time recovery

• sqlfriend

  SSC Guru

  Points: 52588

  More actions

  June 21, 2011 at 10:43 pm

  #238320

  If I do a full backup daily and transaction log backup every 3 hours starting from 6:00 am,

  so the transaction will be done at 6:00 am let's call it trnlog6, 9:00 am trnlog9, 12 pm trnlog12. Now I have a need to restore the database at the state of exactly 10:30 am.

  How can I do that?

  If I restore full backup + trnlog6 + trnlog9+ trnlog12, it will restored to 12:00 pm? Is there a way to restore exactly at the state of 10:30 am?

  Thanks

• Syed Jahanzaib Bin hassan

  SSChampion

  Points: 11410

  More actions

  June 21, 2011 at 10:53 pm

  #1342230

  yes you can

  1-Restore the Full Backup with no recovery

  2-Restore the 6am log backup with no recovery Point in time most recent possible

  3-Restore the 9am log backup with no recovery Point in time most recent possible

  4-Restore the 12pm log backup with recovery point in time stop at 10:00am

  Example

  RESTORE LOG [test Database] FROM DISK = N'C:\logbk' WITH FILE = 1,

  NOUNLOAD, STATS = 10, STOPAT = N'2011-06-22T10:00:00'

  GO

  you can define time in the STOPAT parameter

  Regards,
  Syed Jahanzaib Bin Hassan
  BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

  My Blog
  www.aureus-salah.com