How to Import a BACPAC file to Azure SQL and Overwrite Existing Database?

Question

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?

Answer 1

You are correct. In Azure you cannot restore on an existing database, only "a new or empty one".

• 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.

Answer 2

In Azure you cannot restore on an existing database. (c)

Apparently, not really. You CAN restore a .bacpac file into existing Azure SQL database, for example, in a case when you really need to keep the same instance of Azure SQL database, as it's incorporated in Azure environment. A necessary condition for that is: the target DB must be empty, i.e. like just created as new. But if you already have a target DB, which is not empty (of course!), you can "clean" the database, running the following script in its context, being logged in as a superuser:

DECLARE @sql NVARCHAR(2000)

WHILE EXISTS(SELECT TOP(1) * FROM SYS.TRIGGERS WHERE is_ms_shipped = 0)
BEGIN
    SELECT TOP(1) @sql = 'DROP TRIGGER IF EXISTS [' + [name] + '] ON ' + parent_class_desc COLLATE database_default --SQL_Latin1_General_CP1_CI_AS
    FROM SYS.TRIGGERS
    WHERE is_ms_shipped = 0
    PRINT @sql
    EXEC (@sql)
END

WHILE EXISTS(SELECT TOP(1) * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE')
BEGIN
    SELECT TOP(1) @sql = 'DROP PROCEDURE IF EXISTS [' + ROUTINE_SCHEMA + '].[' + ROUTINE_NAME + ']'
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE = 'PROCEDURE'
    PRINT @sql
    EXEC (@sql)
END

WHILE EXISTS(SELECT TOP(1) * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION')
BEGIN
    SELECT TOP(1) @sql = 'DROP FUNCTION IF EXISTS [' + ROUTINE_SCHEMA + '].[' + ROUTINE_NAME + ']'
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE = 'FUNCTION'
    PRINT @sql
    EXEC (@sql)
END

WHILE EXISTS(SELECT TOP(1) * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY')
BEGIN
    SELECT TOP(1) @sql = 'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']'
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_TYPE='FOREIGN KEY'
    PRINT @sql
    EXEC (@sql)
END

WHILE EXISTS(SELECT TOP(1) * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW' AND TABLE_SCHEMA != 'sys')
BEGIN
    SELECT TOP(1) @sql = 'DROP VIEW [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'VIEW' AND TABLE_SCHEMA != 'sys'
    PRINT @sql
    EXEC (@sql)
END

WHILE EXISTS(SELECT TOP(1) * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME != '__MigrationHistory')
BEGIN
    SELECT TOP(1) @sql = 'DROP TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME != '__MigrationHistory'
    PRINT @sql
    EXEC (@sql)
END

--SELECT * FROM SYS.TYPES WHERE is_user_defined = 1
--SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME != '__MigrationHistory'
--SELECT * FROM SYS.TRIGGERS WHERE is_ms_shipped = 0
--DROP TRIGGER IF EXISTS [backup_objects] ON DATABASE

Last 4 statements are optional. First 3 of them are for checking/viewing of some parameters, and last one is used for dropping a specific database-level trigger if it exists.

After that, you can restore the .bacpac file you have, to the target DB using the standard sqlpackage.exe utility, running it in the Administrative-elevated command line window, like this:

“C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\sqlpackage.exe” /a:Import /sf:"C:\Users\[username1]\Documents\SQL Server Management Studio\DAC Packages\[SourceDBName.bacpac]" /tsn:[azureSQLServerName].database.windows.net /tdn:[TargetAzureSQLDBName] /tu:[DBAdminLogin] /tp:[DBAdminPassword]

Just replace [braced_values] with real ones, and it should work. Upon running, the utility may show a warning message, displayed with yellow color, like this:

Initializing deployment The object [data_0] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box. The object [log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

You can just disregard it. Depending of the database size and complexity, the restoring process may take a long time, so make sure to wait until it finishes, displaying the message: Successfully imported database.

Hope that helps.