April 25, 2019 at 8:09 pm
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
April 26, 2019 at 1:24 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 26, 2019 at 2:12 pm
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.
April 29, 2019 at 4:21 pm
Thanks Grant. That's what Microsoft just confirmed for me as well. Oh well
April 29, 2019 at 4:34 pm
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
April 29, 2019 at 4:36 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2019 at 4:42 pm
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
May 2, 2019 at 1:37 pm
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply