migrate sql server database to azure sql database

Multi tool use
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?


add a comment |
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?


add a comment |
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?


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?




asked Jan 2 at 11:29
thotwielderthotwielder
74942352
74942352
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
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:TempSampleDatabase.bacpac"
Next upload the bacpac to the storage account using PowerShell.
Set-AzureStorageBlobContent -Container “mycontainer” -File “F:TempSampleDatabase.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
your script seems not complete. And I have tried the powershell script in docs.microsoft.com/en-us/azure/sql-database/sql-database-import but got this errorNew-AzureRmSqlDatabaseImport : Object reference not set to an instance of an object.
– thotwielder
Jan 2 at 22:48
add a comment |
In addition to Import/Export you can use Snapshot Replication or Transactional Replication. See Replication to SQL Database single and pooled databases.
Do you have a working example?
– thotwielder
Jan 3 at 3:50
add a comment |
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.
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54005534%2fmigrate-sql-server-database-to-azure-sql-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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:TempSampleDatabase.bacpac"
Next upload the bacpac to the storage account using PowerShell.
Set-AzureStorageBlobContent -Container “mycontainer” -File “F:TempSampleDatabase.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
your script seems not complete. And I have tried the powershell script in docs.microsoft.com/en-us/azure/sql-database/sql-database-import but got this errorNew-AzureRmSqlDatabaseImport : Object reference not set to an instance of an object.
– thotwielder
Jan 2 at 22:48
add a comment |
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:TempSampleDatabase.bacpac"
Next upload the bacpac to the storage account using PowerShell.
Set-AzureStorageBlobContent -Container “mycontainer” -File “F:TempSampleDatabase.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
your script seems not complete. And I have tried the powershell script in docs.microsoft.com/en-us/azure/sql-database/sql-database-import but got this errorNew-AzureRmSqlDatabaseImport : Object reference not set to an instance of an object.
– thotwielder
Jan 2 at 22:48
add a comment |
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:TempSampleDatabase.bacpac"
Next upload the bacpac to the storage account using PowerShell.
Set-AzureStorageBlobContent -Container “mycontainer” -File “F:TempSampleDatabase.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
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:TempSampleDatabase.bacpac"
Next upload the bacpac to the storage account using PowerShell.
Set-AzureStorageBlobContent -Container “mycontainer” -File “F:TempSampleDatabase.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
answered Jan 2 at 12:36


Alberto MorilloAlberto Morillo
6,9551918
6,9551918
your script seems not complete. And I have tried the powershell script in docs.microsoft.com/en-us/azure/sql-database/sql-database-import but got this errorNew-AzureRmSqlDatabaseImport : Object reference not set to an instance of an object.
– thotwielder
Jan 2 at 22:48
add a comment |
your script seems not complete. And I have tried the powershell script in docs.microsoft.com/en-us/azure/sql-database/sql-database-import but got this errorNew-AzureRmSqlDatabaseImport : Object reference not set to an instance of an object.
– thotwielder
Jan 2 at 22:48
your script seems not complete. And I have tried the powershell script in docs.microsoft.com/en-us/azure/sql-database/sql-database-import but got this errorNew-AzureRmSqlDatabaseImport : Object reference not set to an instance of an object.
– thotwielder
Jan 2 at 22:48
your script seems not complete. And I have tried the powershell script in docs.microsoft.com/en-us/azure/sql-database/sql-database-import but got this errorNew-AzureRmSqlDatabaseImport : Object reference not set to an instance of an object.
– thotwielder
Jan 2 at 22:48
add a comment |
In addition to Import/Export you can use Snapshot Replication or Transactional Replication. See Replication to SQL Database single and pooled databases.
Do you have a working example?
– thotwielder
Jan 3 at 3:50
add a comment |
In addition to Import/Export you can use Snapshot Replication or Transactional Replication. See Replication to SQL Database single and pooled databases.
Do you have a working example?
– thotwielder
Jan 3 at 3:50
add a comment |
In addition to Import/Export you can use Snapshot Replication or Transactional Replication. See Replication to SQL Database single and pooled databases.
In addition to Import/Export you can use Snapshot Replication or Transactional Replication. See Replication to SQL Database single and pooled databases.
answered Jan 2 at 20:38
David Browne - MicrosoftDavid Browne - Microsoft
16k2627
16k2627
Do you have a working example?
– thotwielder
Jan 3 at 3:50
add a comment |
Do you have a working example?
– thotwielder
Jan 3 at 3:50
Do you have a working example?
– thotwielder
Jan 3 at 3:50
Do you have a working example?
– thotwielder
Jan 3 at 3:50
add a comment |
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.
add a comment |
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.
add a comment |
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.
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.
answered Jan 3 at 8:41
JarnstromJarnstrom
32715
32715
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54005534%2fmigrate-sql-server-database-to-azure-sql-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
DQx4GuJkPYing2qqQ9 t,MNfM4b0Piy,3Xn0