migrate sql server database to azure sql database

Question

We have a sql server on vm, and a azure sql database, now we want to migrate/restore the vm db to the azure db. I know how to do that via ssms manually. But we want to schedule it because we need to run it daily. I can't find the t-sql scripts to do these. Is there any way?

Answer 1

You can use below PowerShell scripts with Azure Automation to schedule the creation of the bacpac and the import process to Azure SQL Database.

First you need to create a Blob storage account, then create a container on the storage account.

Set-AzureSubscription -CurrentStorageAccountName “YourStorageAccountName” -SubscriptionName $subscription 

New-AzureStorageContainer -Name “mycontainer” -Permission Off 

Now you need to create a bacpac using SqlPackage. You can schedule the creation of the bacpac using Schedule Tasks on Windows or using PowerShell.

SqlPackage /Action:Export /SourceServerName:SampleSQLServer.sample.net,1433 /SourceDatabaseName:SampleDatabase /TargetFile:"F:\Temp\SampleDatabase.bacpac"

Next upload the bacpac to the storage account using PowerShell.

Set-AzureStorageBlobContent -Container “mycontainer” -File “F:\Temp\SampleDatabase.bacpac” 

Now you can import the bacpac to the Azure SQL Database logical server as a new database:

Get-AzureStorageKey –StorageAccountName “YourStorageAccountName” 

$primarykey=(Get-AzureStorageKey -StorageAccountName “YourStorageAccountName”).Primary 

$StorageUri=(Get-AzureStorageBlob -blob ‘SampleDatabase.bacpac’ -Container ‘mycontainer’).ICloudBlob.uri.AbsoluteUri 

Answer 2

In addition to Import/Export you can use Snapshot Replication or Transactional Replication. See Replication to SQL Database single and pooled databases.

Answer 3

You can use SQL Data Sync, it will do several synchronizations per day, you configure the interval yourself.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-sync-data

Hybrid Data Synchronization: With Data Sync, you can keep data synchronized between your on-premises databases and Azure SQL databases to enable hybrid applications. This capability may appeal to customers who are considering moving to the cloud and would like to put some of their application in Azure.