1

I have a backup file that came from Server A and I copied that .bak files into my local and setup that DB into my Sql Server Management Studio. Now After setting it up I deployed it in Azure Sql Database. But now there were change in the Data in Server A because it's still being used, so I need to get all those changes to the Azure SQL Database that I just deployed. How am I going to do that?

Note: I'm using Azure for my server and I have a local copy of Server A database. So basically in terms of data and structure my local and the previous Server A db is the same. But after a few days Server A data is now updated and my local DB is still the same as when I just backup the db in Server A.

How can I update the DB in Azure to take all the changes in Server A and deploy it in Azure?

jarlh
  • 42,561
  • 8
  • 45
  • 63
MadzQuestioning
  • 3,341
  • 8
  • 45
  • 76
  • This is a valid question but your problem is likely that you didn't script your changes from start. So you will need to create a script that makes the changes you want to do on your Database and run that. There is no good way to compare changes in structure between to databases and apply the difference to one of them what I'm aware of. The alternative is to do backup and restore but then you will loose the data in the target database. – Daniel Björk Aug 20 '20 at 09:54

2 Answers2

1

You've got a few choices. It's just about migrating data. It's also a question of which data you're going to migrate. Let's say it's a neat, complete replacement. Then, I'd suggest looking at the bacpac mechanism. That's a way to export a database, it's structure and data, then import it into a new location. This is one mechanism of moving to Azure.

If you can't simply replace everything, you need to look at other options. First, there's SSIS. You can build a pipeline to move the data you need. There's also export and import through sqlcmd, which can connect to Azure SQL Database. You can also look to a third party tool like Redgate SQL Data Compare as a way to pick and choose the data that gets moved. There are a whole bunch of other possible Extract/Transform/Load (ETL) tools out there that can help.

Grant Fritchey
  • 2,645
  • 19
  • 21
  • Thanks for the reply. The problem is I don't know what changes were made and what table it is... If there were ways to just take the difference between the two and just deploy it or something – MadzQuestioning Aug 20 '20 at 10:37
  • @MadzQuestioning Isn't that what any data comparison tool will achieve as you set the source and target data sources for comparison? https://documentation.red-gate.com/sdc/setting-up-the-comparison/setting-data-sources – David Atkinson Aug 21 '20 at 09:47
  • If you don't know where the changes are, then you absolutely need a comparison tool like Redgate SQL Data Compare. It can identify where the changes are. You do have to know where your databases are. It can't find 'em for you. However, within your databases, it can identify differences in data between two of them. – Grant Fritchey Aug 21 '20 at 12:15
1

Do you want to sync schema changes as well as Data change or just Data? If it is just Data then the best service to be used would be Azure Data Migration Service, where this service can help you copy the delta with respect to Data to Azure incrementally, both is online and offline manner and you can also decide on the schedule.

Hassan Raza
  • 412
  • 2
  • 6