This is a part of series “Stairway to being an Azure SQL DBA“, where I will be covering all the topics that an Azure SQL DBA should know about.
A point in time restore is basically bringing back the database to a state before the “Oops” moment. This Oops moment could be someone running a query required to be run on non-prod to a prod one, or a change which instead of fixing one thing broke many other, or just a simple request to bring back the to a prior state.
On an On-Prem environment the steps involve restoring the full backup, then restoring the differential backup and then all the transaction log backups until the time we want it to be restored and then stopping it an specific time using either the ‘STOPATMARK’ or ‘STOPAT’ hint in restore log command.
As you already know that in Azure SQL Database we have only Automatic Backups and can say Automatic Restores. You just need to mention the time to which you want to restore and the rest is taken care by Azure.
The first important thing to understand is you can do point-in-time restore in the time specified in the PITR backups. Below is what is setup for the database for which I’ll be doing PITR
As it’s setup for only 7 days that means PITR is possible for 7 days prior to today only.
To do PITR
- Goto your database and click on Restore button.
2. Azure doesn’t replace the database being restored but creates another database with time timestamp suffix. You can also choose the compute and storage along with naming to something which make more sense to you.
The important thing is the “Restore Point” which is as the name suggests is the time for point-in-time restore. Which is going to be the time before the “Oops” moment or the request that you have received.
Once you click on the Review+Create it will give the summary and then click on create to be start the restore, and being the scene Azure will choose the appropriate Full backup, Differential backup and Transaction log backups to do the PITR.
I hope it helps!
being a dba 