Two of my tables suddenly had all data within them deleted. These tables where created over a year ago. Both tables have not changed in any way over the last year. These tables are queried daily by a power automate flow. Looking at the recent commands that have been run on the database I see a bunch of drop extent commands that are run automatically (not sure if this is the cause).
Questions:
How can I determine the reason that the data in both these tables was deleted?
If you have database-admin / database-monitor privileges, you can look at the Journal and look for operations that dropped extents (data shards) from the table.
Looking at the recent commands that have been run on the database I see a bunch of drop extent commands that are run automatically (not sure if this is the cause).
This likely indicates that there's a retention policy with a non-infinite soft delete period configured on this database/table. if you wish for data to be retain beyond this period, you'll need to alter the retention policy.
How can I recover the data that was deleted?
If the table had recoverability set to enabled in its effective retention policy, and less than 14 days have passed since the data was dropped - you can open a support ticket for requesting to recover the data.