You are here: Home / Blog / posts / Programming Magic / How to Copy a Windows Azure Database

How to Copy a Windows Azure Database

by admin published Oct 09, 2013 05:45 PM, last modified Jan 28, 2014 11:41 AM
Describes how to copy a Windows Azure database from the Management Portal, specifically Pre-Purchasing.

Log into master database:

  • Enter "https://qb5fm1u0eb.database.windows.net" in a web browser.
  • The Windows Azure SQL Database log on page appears.
  • Enter "master" for database, username = "opp", and password. (see the Accounts.txt file)
  • Select "Log on" The Windows Azure master database page appears.

Copy the Database:

  • Select "New Query", and enter the following text:
  • CREATE DATABASE PrePurchasingBackup AS COPY OF qb5fm1u0eb.PrePurchasing
  • A message like: "Command(s) completed successfully" should appear in the "Messages" pane.
  • What this actually means is that the command has been queued to be processed and will happen asynchronously. You can view status back on the main SQL Azure management portal window under "Databases". It will typically say "Copying..." for some amount of time depending on the size of the database.

Exporting and Restoration

  • Once the database copy has been competed the database may be exported to a BACPAC file; however, restoring the database from the BACPAC file will fail with an error, i.e., Error encountered during the service operation. Data plan execution failed with message One or more errors occurred. Violation of PRIMARY KEY constraint 'PK_Attachments'. Cannot insert duplicate key in object 'dbo.Attachments'. The duplicate key value is (31ad9183-0a2a-4a43-869f-a18d00f950bb). The statement has been terminated., if there are any database triggers enabled that cause records to be inserted when some other record is inserted.  This would most likely be the case when DataSync is being utilized to update the database.
  • The solution to avoiding these types of errors is to disable the offending triggers. I recommend disabling ALL database triggers prior to export, and then re-enabling them once that database has been restored.
  • This procedure is described in the following items:

Log into the PrePurchasingBackup database:

Once the database copy has been completed log into the PrePurchasingBackup database as follows:

  • Enter "https://qb5fm1u0eb.database.windows.net" in a web browser.
  • The Windows Azure SQL Database log on page appears.
  • Enter "PrePurchasingBackup" for database, username = "opp", and password. (see the Accounts.txt file)
  • Select "Log on"
  • The Windows Azure PrePurchasingBackup database page appears.

Disable All Database Triggers:

Once logged into the PrePurchasingBackup database disable all database triggers as follows:

  • Select "New Query", and enter the following text:
  • DISABLE TRIGGER ALL ON DATABASE
  • A message like: "Command(s) completed successfully" should appear in the "Messages" pane.
  • What this actually means is that the command has been queued to be processed and will happen asynchronously.

Log into the Windows Azure Management Portal:

Once the database copy has been competed, log into the Windows Azure Management Portal as follows

  • Enter "https://manage.windowsazure.com" in a web browser.
  • The Windows Azure Management Portal log on page appears.
  • Enter your Windows Live username, and password.
  • Select "Log on"
  • The Windows Azure Dashboard "all items" page appears.

Exporting the Backup Database, i.e. PrePurchasingBackup

Now that all database triggers have been disabled from the backup database it may be exported as follows:

  • From the "All Items" page select DB: SQL DATABASES.
  • Select the PrePurchasingBackup database.
  • The PrePurchasingBackup page appears.
  • Select "Export" from the bottom of the page.
  • The "Export Database Settings popup appears.
  • Modify the BACPAC file name if different from the one automatically populated
  • Select appropriate BLOB STORAGE ACCOUNT, i.e., prepurchasing (location = West US) or select Create a new storage account*. *Note: If creating a new one, make sure you select the same location as the database; otherwise, you will be charged for moving data to a different region.
  • Choose a container, i.e., database-backups or select Create a New Container.
  • Enter the SERVER LOGIN NAME, i.e. "opp", and PASSWORD* (*see the Accounts.txt file for the password).
  • Select the "check" icon to login.
  • The export process will begin and the prepurchasingbackup database page will reappear.*
  • *The export status is indicated by a status icon, i.e., a set of fluctuating horizontal bars at the lower right-hand side of the page next to the question mark (?) icon.
  • You can select the status icon and the status messages will appear.
  • For additional details you can select DETAILS or the information (i) icon and list of status details will appear.*
  • *Note: As far as I can tell, the status messages and details are only present in the same session that you create the database export. Therefore, do not close the webpage a long as you want to view the export status.

Restoring a Windows Azure database from a BACPAC archive

The process for restoring a Windows Azure database is similar to exporting a database, except in reverse, and selecting "IMPORT" as opposed to "EXPORT".

  • Log into the Windows Azure Management Portal as described previously.

Enabling All Database Triggers:

Once the database has been fully restored, the all the database triggers need to be re-enabled as follows:

  • Log into the restored database as described in the "Log into the PrePurchasingBackup database" section above, except modify the database name to that of the restored database, i.e. PrePurchasing.
  • The Windows Azure database page appears for the restored, i.e. PrePurchasing, database.
  • Select "New Query", and enter the following text:
  • ENABLE TRIGGER ALL ON DATABASE
  • A message like: "Command(s) completed successfully" should appear in the "Messages" pane.
  • At this point all the database triggers should be enabled and the restoration process is complete.

When:

Where:

Contact