How to restore mssql db backup

Question

We have below backup policy.

full backup 12:30 pm.

every 4 hour differential backup

every 30 min transaction backup.

if my database fails at 7:00 PM what be the procedure to restore backup with minimal data loss.

I have 12:30 pm full backup, 4 pm differential backup and every 30 mins transactional logs backup

Answer 1

The order to restore the backups is

• latest full backup
• latest differential backup
• all log backups since the last different backup
• the latest log backup is restore with option "with recovery" to end the restore chain

Answer 2

Hi @Satish Bansode

Thanks for your question.

Based on the backup policy you have provided, if your database fails at 7:00 PM, you can restore the database to the latest transaction log backup which was taken at 6:30 PM. Here are the steps to restore the database:

To restore the database with minimal data loss in the event of a database failure at 7:00 PM, you would follow the below procedure:

Identify the most recent full backup taken before 7:00 PM. In this case, it would be the backup taken at 12:30 PM.

Restore the most recent full backup (with no recovery) taken at 12:30 PM

Apply the most recent differential backup (with no recovery) taken after the full backup. In this case, it would be the differential backup taken closest to 7:00 PM (e.g., the backup taken at 4:00 PM).

Apply any transaction log backups taken after the differential backup. These transaction log backups capture all the database changes since the last full backup.

Repeat step 4 for each transaction log backup taken between 4:00 PM and 7:00 PM. Apply them in chronological order to restore the database to the most recent state possible.

By following this procedure, you can restore the database with minimal data loss, as it includes the changes captured in the differential backups and transaction log backups taken before the failure occurred.

Hope this helps!

Thank you!