4

Say I have a local database named MyDatabase. I want to move it to Azure SQL and replace an existing database currently residing there named MyDatabase. I know how to create a BACPAC file locally. I know how to import the BACPAC to my Azure storage account. However, once the BACPAC is in Azure storage, I don't know the preferred way of overwriting the existing MyDatabase database with the copy in storage. I could import the BACPAC file and create a second database, then delete the first, and rename the database just imported. However, is the best or preferred way of doing this?

8

You are correct. In Azure you cannot restore on an existing database.

  • You have to restore with a different name.
  • Delete old database
  • Rename new one to old database name.

You have few ways to restore from .BACPAC file.

  1. You can do it directly from your on prem .BACPAC location by using .\sqlpackage.exe command line tool.

    .\sqlpackage.exe /a:Import /sf:C:\filename.bacpac /tsn:ServerName.database.windows.net /tdn:destinationDBName ` /tu:adminaccountName@serverName /tp:$credentialPW

  2. You can also use the copy that you uploaded in your storage account.

    $ResourceGroupName = "RGName" $ServerName = 'ServerName' $DatabaseName = "DestinationDBName"

    $StorageName = "StorageAccountName" $StorageKeyType = "StorageAccessKey" $StorageUri = "http://$StorageName.blob.core.windows.net/swwstoragecontainer/BackpacFileName.bacpac" $StorageKey = "***********************************"

    $credential = Get-Credential

    $importRequest = New-AzureRmSqlDatabaseImport -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName -StorageKeytype $StorageKeyType -StorageKey $StorageKey -StorageUri $StorageUri -AdministratorLogin $credential.UserName -AdministratorLoginPassword $credential.Password ` -Edition Standard -ServiceObjectiveName S0 -DatabaseMaxSizeBytes 50000

    Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $importRequest.OperationStatusLink

  3. In portal you can directly import the .BACPAC file into your server as a database.

  • 1
    It appears I can also use SSMS to deploy my local copy to Azure SQL by right-clicking on the database in SSMS, choosing Tasks and then Deploy Database to Microsoft SQL Azure. Have you done this? – Randy Minder May 27 '17 at 20:21
  • 1
    You are correct, yes I did. This is one of the easiest way I show in my presentation. Details here. – SqlWorldWide May 27 '17 at 22:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.