When doing restore we always get a new database ID, Physical Name and SqlEndPoint, even if the database logical name is the same (eg restore over the top). This causes a massive increase in RTO...
MDC70
Advocate I
Advocate IPlease enable the "Overwrite the existing database WITH REPLACE" functionality, as we know it from SQL Server. When a restore is performed into a different database, all references in the ETL might have to be adjusted - which is something nobody wants to deal with. Therefore, having the WITH REPLACE option would be very useful
StrahinjaRodicMicrosoft Employee
Hi, MDC70, Thanks for the feedback!
We'd like to understand better this feedback and answering these questions would help us a lot:
- When you say "All references in the ETL" - do you need to restore over the existing database with the same name to preserve downstream ETL references or for something else?
- If restoring to a new database and renaming it was automated and seamless, would that meet your needs? If not, why do you specifically require true in-place overwrite?
- Would you be okay with permanently losing all data changes made after the restore point?
- What are your expectations around downtime and automation for this feature? Would you plan a maintenance window and manually restart ETL jobs, or are you aiming for a near-zero-downtime restore that seamlessly continues ETL without reconfiguring connections?
Thank you,
Strahinja Rodic - PM on Backup & Restore area on SQL database in Microsoft Fabric
- MDC70
Hi Strahinja I'm happy to provide feedback on your questions and hope this makes things clearer for you.
- Correct! I was specifically thinking of the references in a data pipeline. After the restore, if a new database appears in the workspace with a new name, we end up with two databases -the original and the restored one. If the ETL is supposed to run on the restored database, then every activity that references the database must be updated accordingly.
- I need to answer this a bit more descriptively, from the perspective of an experienced DBA. When performing a restore, I want to decide for myself whether the restore should create a new database or overwrite the existing one - essentially just like it's possible with any standard database.
- Yes, that would be okay. We assume that we have a daily processing routine - for example, an ETL job that runs for 4 hours. After that, the data is available for querying. The source data can always be reloaded, so we wouldn’t experience any data loss. Here too, the system shouldn’t make any assumptions regarding how the process is handled.
- A short downtime would of course be desirable. We would naturally perform this during a maintenance window and monitor the ETL process afterwards.
Kind Regards Marco
- Correct! I was specifically thinking of the references in a data pipeline. After the restore, if a new database appears in the workspace with a new name, we end up with two databases -the original and the restored one. If the ETL is supposed to run on the restored database, then every activity that references the database must be updated accordingly.
