If you’ve ever used a database, you’ve probably experienced that heart-jolting moment when you accidentally drop a table or run an update statement without a WHERE clause. In that instant, the table is either gone or potentially corrupted beyond repair. Fortunately, there are ways to recover data, depending on the environment you’re working in.

In traditional databases like on-prem SQL Server, or other systems that rely on physical files, restoring a database from a backup file and moving the necessary table back to its original location is straightforward. In the Azure world, Azure SQL Databases and Azure Synapse Dedicated SQL Pools offer restore points, which let you restore data to a new database and then copy the table back to the original one.

However, in Fabric Warehouses, the process is a bit different. While restore points are available, they only allow restoration within the original Warehouse. They don’t allow users to restore the point to another Fabric Warehouse and then copy the table back to the original warehouse. This limitation means we need to take a different approach.

 

Restoring a table when it still exists

If the table hasn’t been dropped, you can use time travel to revert the table to a specific point in time. This feature allows you to access historical versions of your data. The process involves using the CLONE OF command to create a copy of the table as it existed at a given moment. Here’s an example:

This statement clones the Dim.Product table as it was at 1 PM UTC on 24th January 2025, saving it as Dim.Product20250124. Once cloned, you can replace the data in the original table with the data from the cloned table.

Note: Time travel is only available for up to 30 days.

 

Restoring a table when it has been dropped

If the table has been dropped and you can’t access it through time travel, you will need to rely on restore points to recover the table. However, since restore points affect the entire warehouse, we need to carefully follow these steps to avoid overwriting other tables:

  1. Take a restore point now (to capture the current state of the warehouse).
  2. Restore the warehouse to the point in time when the table was still intact.
  3. Write the recovered table to a new Fabric Warehouse within the same workspace. (If necessary, create a temporary warehouse for this step).
  4. Restore the warehouse to the restore point you just took (i.e. the current state).
  5. Move the restored table from the temporary warehouse back to your original warehouse.

Note: Restore points are only available for up to 30 days.

To take a restore point, navigate to the Fabric Warehouse and go to settings. Next, go to Restore points and add a restore point.

 

Summary

Restoring a table in a Fabric Warehouse may seem complex due to the limitations with restore points and time travel, but with a strategic approach, it is entirely possible. Whether the table still exists or has been dropped, using time travel and restore points effectively will allow you to recover your data without disruption.

Remember, both time travel and restore points are only available for up to 30 days, so it’s essential to act quickly when data needs to be recovered.

Tags: , ,

« Previous Post
Next Post »