Recovering data after manual errors

Question

Recovering data after manual errors

Naveenkumar Senapathy 20

Hi,

Our Azure SQL database is setup for Zone-redundant backup storage. I wanted to know if it is possible to recover data from a short period in the past like 30 mins/1 hour. This will be required for us in case of data error caused by an user error or a bug in application that was just deployed. I know we have automatic backup that holds backup (PIT) for every 12 hours but that would give a huge loss in data in case if recover data that is 12 hours old.

The documentation that I have read shows no possibility but wanted to check with the community.

Thanks,
Naveen

RahulRandive 10,506 Reputation points

2023-11-21T13:25:12.5666667+00:00

Hi Naveenkumar Senapathy,

Please refer to the discussion in the thread below for an answer.

You may need to test in your non-production environment. However, if the data is syncing rapidly (as expected), you may not be able to restore a backup from 30 minutes or 1 hour ago and rely on point-in-time restore only.

https://stackoverflow.com/questions/59087203/azure-sql-database-backups-and-restore#:~:text=As%20a%20result%2C%20the%20restored,be%20using%20failover%20groups.

As you aware the main purpose of Geo-redundancy to helps protect against outages that affect backup storage in the primary region. It also allows you to restore your databases in a different region in the event of a regional outage.

Zone-redundant storage (ZRS): Copies your backups synchronously across three Azure availability zones in the primary region and Geo-restore allows you to recover from a regional outage when you can't access your database or backups in the primary region.

Thank you!

Answer accepted by question author

0 additional answers

Your answer

RahulRandive 10,506 Reputation points

2023-11-21T13:25:12.5666667+00:00

Hi Naveenkumar Senapathy,

Please refer to the discussion in the thread below for an answer.

You may need to test in your non-production environment. However, if the data is syncing rapidly (as expected), you may not be able to restore a backup from 30 minutes or 1 hour ago and rely on point-in-time restore only.

https://stackoverflow.com/questions/59087203/azure-sql-database-backups-and-restore#:~:text=As%20a%20result%2C%20the%20restored,be%20using%20failover%20groups.

As you aware the main purpose of Geo-redundancy to helps protect against outages that affect backup storage in the primary region. It also allows you to restore your databases in a different region in the event of a regional outage.

Zone-redundant storage (ZRS): Copies your backups synchronously across three Azure availability zones in the primary region and Geo-restore allows you to recover from a regional outage when you can't access your database or backups in the primary region.

Thank you!

Answer 1

GeethaThatipatri-MSFT 29,597 Microsoft Employee Moderator

Hi @Naveenkumar Senapathy, Welcome to Microsoft Q&A thanks for posting your question.

Azure SQL DB provides capability to restore database copy to any point in time within the retention period as defined:

https://learn.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups?view=azuresql&tabs=azure-portal#point-in-time-restore

I was wondering if there's a chance that certain details in our documentation might have given the impression that we don't offer Point-in-Time Recovery (PITR). Your insights on this would be greatly appreciated.

Regards

Geetha

Naveenkumar Senapathy 20 Reputation points

2023-11-22T11:33:42.69+00:00

Hi,

No, my question was not about the PITR offering, it was about manual restore of data in case of human error and a need to restore a data that was not covered by the latest PITR backup.

Regards,

Naveen
GeethaThatipatri-MSFT 29,597 Reputation points Microsoft Employee Moderator

2023-11-27T15:48:20.76+00:00

Hi @Naveenkumar Senapathy ,

I’m not following, Automatic backups are being taken every 5-15 minutes (log backups) based on the database activity, meaning our RPO (recovery point objective, or data loss) is approximately 10 minutes. While differential backups are taken every 12 hours, and full backups are taken every 7 days approximately. With PITR offering you can initiate manually restore of your database backups that will create a new database from that backup. PITR can be performed even 15 minutes ago from current time (T), meaning you can do a restore to T-15min.

I believe that 12 hours you’re referring to are related to RTO (recovery time objective) meaning that restore operation can take up to 12 hours. Read here for more information about restore capabilities and features:

I hope this information helps.

Regards

Geetha
Naveenkumar Senapathy 20 Reputation points

2023-11-29T05:08:40.93+00:00

Hi Geetha,

Thanks for the info. This gets really interesting now. So if I re-iterate my understanding from your comment, we can restore to a point even 15 minutes earlier from current time and it doesn't depend on the differential backup for this restore. This will allow us to restore to a point like 15 or 30 minutes in the past to recover any data error caused by user action. Is my understanding correct?

Regards,

Naveen
GeethaThatipatri-MSFT 29,597 Reputation points Microsoft Employee Moderator

2023-11-30T00:05:00.07+00:00

Hi @Naveenkumar Senapathy

Yes, definitely. The transaction log backups taken every few minutes enable the restore to a point in time even after the last differential backup.

I hope this information helps.

If this answers your question, please consider accepting the solution by hitting the Accept as Solution ‌‌as it helps the community look for answers to similar questions.

Regards

Geetha

Share via

Recovering data after manual errors

0 additional answers

Your answer