Backup and restore option was an important and useful option was missing with SQL Azure before. But this is now introduced as CTP with SQL Azure lab (it is also included with Azure Management portal).
Considering Backup and Restore functionality with SQL Azure, there are multiple ways available now. They are –
- Import and Export option provided with Management Portal
- Data-tire Application (DAC) framework APIs
- pcp utility
- Copy SQL Azure database to another database
Each method is useful for different situations. So having pros and cons of each method will be useful to decide which method to use.
This post explains Import and Export CTP option provided with Management Portal.
Understand the database to export
Below is the image show the database in SQL Azure and the aim is to take the backup of Northwind2 database using Azure Management Portal.
- Verify the database can be accessible from the system (by verify firewall configuration) and login with an user.
- Select the database under the server and press the Export button under the Import and Export CTP tab.
- Once the database backup is completed, the backup file will be stored in Blob Storage service. The database backup file extension will be .bacpac.
The system will popup Export Database to Storage Account.
- Click the Finish button. Once the button clicked, it will show a popup informing the unique ID.
- The status of the backup can known using the portal.To know the status, select the server and press the Status button under Import and Export CTP secion.
- It will open Import Export Request Status popup window. Provide Login Name and Password.
- The screen shows list of request Id with the status on time.By clicking the Refresh button, it will refresh the status each Export and Import line.
It will show the status of last two week Import and Export operations. Note:Sometime, the screen does not show all Export and Import operation status. Sometime it does not complete Export or Import also. Hope this will be corrected in release.
Check the database backup files in Blob
Below is the screen shot of blob service which contains the backup files exported from Management Portal. I use Azure MMC for getting the files from blob storage. The files can be downloaded by download BLOBs option.
Import the database
To import a database, the database backup file (.bacpac) must be in a blob storage.
- Select the Import button under Import and Export CTP.
- It will show a Import Database from Storage Account popup screen.
- Press Finish. It will popup a window informing the request Id to know the status. The status of import operation can known at Status operation explained before.
- Below screen shows the imported database under the server.
As explained, each database can be backup by using Import and Export option provided. But it require some manual operation and it takes some space in the blob storage. So, it will be billed as per the space it takes. To reduce the cost, the exported files must be downloaded from blob storage, but this again will cost for transferring the data.
The database can be backup using DAC utility also and it can run in the local system. The backup files will be stored in the local system as well. To know how to use DAC framework, please verify the next post.
For automating SQL Azure backup using Worker Role, please look at another post Automating SQL Azure backup using Worker Role.