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?
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.
You can do it directly from your on prem .BACPAC location by using
.\sqlpackage.execommand line tool.
.\sqlpackage.exe /a:Import /sf:C:\filename.bacpac /tsn:ServerName.database.windows.net /tdn:destinationDBName ` /tu:adminaccountName@serverName /tp:$credentialPW
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
In portal you can directly import the
.BACPACfile into your server as a database.