Restore Azure SQL Database on a Server to a Different Server

Richard Epstein 2 Reputation points
2022-04-21T13:54:57.193+00:00

I am trying to restore an Azure SQL database called dbABC that is located on Azure SQL Server A and restore the database to Azure SQL Server B as database dbABCRestored. After I select restore (through the Azure Portal) of Azure SQL database dbABC located on Azure SQL Server A, the server dropdown box is disabled and only displays Server A. How do I enable the dropdown to be able to choose other SQL Servers? What permissions do I need on Azure SQL Server A and Azure SQL Server B? What specific server roles must be assigned to each SQL Server? Are there any master database roles that need to be assigned on Azure SQL Server A and Azure SQL Server B?

Azure SQL Database
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator
    2022-04-21T14:05:13.897+00:00

    Azure SQL B won't be able to see backups associated with other servers Azure SQL logical servers. Please proceed to export the database teh database on Server A to an Azure Storage Account. When finish go to the blade on Azure portal that belongs to Server B and proceed to import the database from the Azure Storage Account.

    0 comments No comments

  2. Ronen Ariely 15,206 Reputation points
    2022-04-21T15:57:24.06+00:00

    Hi,

    If you need to restore the database as it is now, then your approach is wrong and you simply need to use CREATE DATABASE AS COPY OF

    You can read more about the action here: Create a copy of a database on another logical server

    If you need to restore a point in time from Logical Server A to Logical Server B (meaning to restore an old backup and not the current database) then you should restore the database point in time to the Server A and then use Create a copy of a database on another logical server

    I do not think that using export and import make sense here. BACPAC has nothing to do with backup. It is a copy of data and schema only. It does not guarantees transactional consistency! BACPAC is a good solution to copy data and schema between environments and not between the same environments. For example when you want to copy data ans schema between On-premises and Azure SQL Database.

    Note! I HIGHLY recommend to search Google for: ronen ariely bacpac. Find one of my recording regarding the internals of BACPAC

    ----------

    14150-image.pngRonen Ariely
    Personal Site | Blog | Facebook | Linkedin


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.