By Wayne Pai (Senior Software Engineer, Microsoft)
In August 2021, we announced general availability (GA) of backup and restore for large datasets. This Power BI feature closes an important gap to Azure AS. You can backup Power BI datasets on a regular basis to meet the data retention and disaster recovery requirements of your organization. You can also use this feature to migrate enterprise BI workloads from Azure AS to Power BI. Another common scenario is to roll back an existing Power BI dataset to a previous version. Yet, rolling back was not without challenges for large datasets near the max dataset size. But thanks to the latest improvements, you can now even restore a backup file when the dataset size is near the SKU limitation. You no longer need to be concerned that size limits impact restorability.
For example, a P3 capacity has a max dataset size of 100 GB. If you wanted to maximize memory usage, you might have intended to host a dataset with 70 GB on this capacity. However, prior to the recent backup and restore improvements, you would find it challenging to accomplish this goal. While you can minimize the memory requirements for refresh operations through advanced refresh techniques, dataset restore operations would still require at least 50% of the available memory, effectively limiting the max dataset size to less than 50 GB. You could host a 70 GB on a P3 capacity. You could backup the dataset regularly to meet the data retention requirements of your organization. But you could not rollback the dataset to a previous version by using restore because there was not enough memory available to load the backup file. Restore would fail because it exceeded the max available memory per dataset on a P3 capacity: 70 GB for the originally loaded database + 70 GB to restore the backup file > 100 GB for the P3 max dataset size).
Fortunately, thanks to the recent backup and restore improvements, you can now use a new /forceRestore option with the restore command to overcome this limitation. The following screenshot shows a corresponding restore command. When restoring a dataset with the /forceRestore option, Power BI will try its best to perform the restore operation even when the available memory is limited. Power BI might unload the original database, temporarily disconnecting the users, but the restore operation will succeed.
If you forget to include the /forceRestore option when attempting to restore a dataset near the size limit, you might get the following error message to remind you that the /forceRestore option is required in this case:
“We cannot restore the dataset backup right now because there is not enough memory to complete this operation. Please use the /forceRestore option to restore the dataset with the existing dataset unloaded and offline.”
Note also that the /forceRestore option is only available when restoring a dataset in Power BI Premium. It is not available with SQL Server Analysis Services or Azure AS. So, the scenario described above would fail on an S4 Azure AS server, while it can succeed on Power BI Premium. If you still have datasets on Azure AS, don’t wait and migrate these datasets to Power BI Premium now! And even if you do not need to migrate Azure AS datasets, we hope that the recent backup and restore improvements enable you to increase the sizes of your Power BI datasets where necessary and maximize in this way your return on investments in Power BI Premium.