cancel
Showing results for 
Search instead for 
Did you mean: 

How to do point in time recovery with SQL Server database?

XTANG
Level 2

Hi everyone,

I am evaluating Symantec Backup Exec 2014. I have a full database backup on Friday, a differential backup on every week day and transaction log backup on every two hours. I need to do point in time recovery. I know I need to restore the full backup, the last differential backup and every transaction log backups since the last differential backup and specify what time I want transaction log to be recovered to.  I am not quite sure what do I need to do with Backup Exec 2014. If I select the “to a point in time in the transaction log up to and including the specified time”, does Backup Exec automatic restore the full backup, the last differential backup and transaction log backups which were taken after last differential backup? What is the correct way to do point in time recovery with full, differential and transaction log backups? Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions

pkh
Moderator
Moderator
   VIP    Certified

 

You will get this dialog at a certain stage in the restore process

BE 2014 - SQL PIT.png

If you don't get this dialog, post screenshots of the sequence of dialogs that you do see.

View solution in original post

7 REPLIES 7

SA_Techie
Level 3

Hi, 

Yes. When you do "Point in time restore" it does restore Full, Differential and the transaction logs till the selected recover point.

You can restore transactions from a transaction log up to and including a point in time in the transaction log. After the point in time is reached, recovery from the transaction log is stopped. To find dates and times of transactions, check your client application event log.


If the specified point in time is later than the time contained in the most recent transaction log being restored, then the restore operation succeeds, but a warning is generated and the database remains in an intermediate state. If the specified point in time is before the time contained in the transaction log or logs being restored,
no transactions are restored.

 

Hope this helps !!

 

pkh
Moderator
Moderator
   VIP    Certified

I am not sure whether BE 2014 will restore the full and differential backups if you just restore the log backup.

1) To be sure, you should restore the full backup and choose the option to to leave the database non-operational

2) Do the same thing with the restore of the differential backup

3) Lastly, restore the log backup up to the time that you want, but this time choose the option to leave the database ready for use.

pkh
Moderator
Moderator
   VIP    Certified
If you want to quote from the Admin Guide do provide the page from which you are quoting from

XTANG
Level 2

Thank you - PKH.

I think the way you mentioned works well with version 2010. With version 2014, I can restore the full and leave the DB non-operational then restore the differential with DB non-operational and finally restore the log and leave the database ready for use. The same way. The problem with this approach is that I don't have the option to do the point in time recovery. Backup exec will restore backups you select - full, differential and log. I don't have the option to let the system know I only need to restore the log to a certain time. For example, the log backup is taken at 4pm. I want to restore the backup to 3:30pm that is one minutes before a table is dropped from the DB. Could someone give me some ideas? Did I miss something? Thanks.

XTANG
Level 2

Thank you very much SA-Techie.

I still have questions. I hope you or someone out there can help me.

1. Do you mean that with point in time restore option that I can only specify the time contained in the most recent transaction log backup? because I don't see any the other option that I can choose other than just specify a time.

2. If the answer is true above, then what can I do if I want to do point in time recovery to the time that is before the time contained in the last transaction log backup?

Thanks again.

pkh
Moderator
Moderator
   VIP    Certified

 

You will get this dialog at a certain stage in the restore process

BE 2014 - SQL PIT.png

If you don't get this dialog, post screenshots of the sequence of dialogs that you do see.

XTANG
Level 2

Thank you again pkh. I saw the Point in time restore option, but I thought if I select the last option "To an individual backup set..." I can also do the point in time recovery. It looks like that I was wrong.  If I select the last option, I can only recover data to the transaction log backup which I choose to restore not to the point in time. Thank you for your help.