Sql-server – How tomport a BACPAC file to Azure SQL and Overwrite Existing Database

azure-sql-databasesql server

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?

Best Answer

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.

Related Question