'Challenges with Azure SQL Managed Instance

  1. Unable to make a copy of the database using the following SQL command or through the Azure portal

    • CREATE DATABASE mydatabase_copy AS COPY OF mydatabase;

  2. Unable to make a copy of the database on Azure SQL Server, no wizard is present

    • we know there is a way of creating bacpac and restoring it, but this is a complete manual process and take too much time
    • we need some automated way to achieve this.
azureazure-sql-managed-instance


Solution 1:[1]

CREATE DATABASE ... AS COPY OF is not available in Managed Instance, only in SQL Database. Instead use Point-in-time restore

Use point-in-time restore (PITR) to create a database as a copy of another database from some time in the past. This article describes how to do a point-in-time restore of a database in Azure SQL Managed Instance.

Point-in-time restore is useful in recovery scenarios, such as incidents caused by errors, incorrectly loaded data, or deletion of crucial data. You can also use it simply for testing or auditing. Backup files are kept for 7 to 35 days, depending on your database settings.

Which you can automate with PowerShell.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 David Browne - Microsoft

Related Questions