How to restore an on-premise database to an existing Azure SQL Database?

  • DBADave

    SSChampion

    Points: 10962

    Hi,

    We used the Data Migration Assistant to migrate on-prem databases to Azure SQL Database.  Now we wish to restore those databases so to refresh the data and schemas.  It appears the Data Migration Assistant will only do this if all the objects in the database have been dropped or the DB doesn't exist.  Is this correct?  If so, what's the best approach to refreshing an Azure SQL Database from an on-prem backup?

    Thanks,  Dave

  • Grant Fritchey

    SSC Guru

    Points: 395320

    You can't.

    You can use migration to replace it. You can use Azure Sync, but you have to set that up and program it. You can use something like Redgate Data Compare. You can restore locally, export as a bacpac file and then replace it using that. However, there's no restore.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714698

    Grant is correct. You're trying to think about this incorrectly, according to what MS has built.

    Azure SQL DB <> SQL Server database on premise

    In fact, the version of Azure SQL DB is often greater than SQL Server db on premise because they deploy changes there first, so it's like perpetually v.Next and not a version that matches 2016, 2017, etc.

    Even in Azure, I can't restore a backup of Azure SQL DB over an existing db. The paradigm has changed. If you're testing things and looking to move to Azure SQL DB from on premises, you're going to be constantly migrating to new dbs, not restoring/refreshing old ones.

  • DBADave

    SSChampion

    Points: 10962

    Thanks Grant.  That's what Microsoft just confirmed for me as well.  Oh well

  • DBADave

    SSChampion

    Points: 10962

    Steve,

    We are essentially performing a POC of MI to determine if it is a viable solution for a project underway to migrate our first of several production SQL Servers to Azure.  For the sake of a POC we need to perform refreshes from on-prem to Azure.  But even after the POC completes within the next week or two, and assuming we proceed with the move to MI, it will take several months to migrate all of the databases on this server, along with their correspond applications, to MI.  During this time we will need to refresh the MI environment to address various user testing scenarios.  This will be a typical approach for all future migrations to Azure, and necessitates the need to find an efficient way to restore the MI databases.

    Thanks,  Dave

  • Grant Fritchey

    SSC Guru

    Points: 395320

    Wait. Managed instance is different. They want that to be a lift & shift location. You can run a restore there. That's different than Azure SQL Database.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • DBADave

    SSChampion

    Points: 10962

    My mistake.  We have several Azure initiatives underway and I just mixed-up two of the projects.  The restore issue is for Azure SQL Database.  We have a different issue for Manage Instance related to Azure AD and Windows Auth, but I'll leave that out of this conversation.  This particular project is also a POC, but lasting several weeks.  The data scientists are requesting periodic refreshes of the databases in Azure SQL DB.  Sorry for the confusion guys.

    Dave

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714698

    No worries. Juggling projects can get confusing. I wish that they supported refreshes of some sort, but they really didn't architect this in and not likely going to in the future.

    What you might be able to do is disguise the current db with DNS and give that to your users. Then as you create a new db with new data, repoint DNS to the new one and drop the old one.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply