Convenient way to dump Azure SQL DB database

Question

Using geo-replication is a nice way of duplicating data but it requires the two servers to be registered on the same Azure account. That could be a potential security risk if an employee decides to log in and delete both servers. In that case there would be no possible way of recovering data.

To counter this potential threat, a scheduled dump sounds like a good way to keep data in another account that another set of users have access to.

Using Database Sync results in new tables being generated and seems messy.

Does Azure SQL DB provide any convenient way of dumping data?

Answer 1

You can create an automation account and runbook which runs a task to export the backup of databases to a storage account every so often. Here is an example:

$ResourceGroupName = "rg" $ServerName = "sqlserver" $StorageKeytype = "StorageAccessKey" $StorageKey = "examplestoragekey" $sqldbs = ("userdb1,userdb2") $UserName = "sqladminaccount" $BacpacUri_Stem = "https://example.blob.core.windows.net/sqlbackup/" $Password = Get-AutomationPSCredential -Name 'MyCredential' foreach ($sqldb in $sqldbs){ $BacpacUri = $BacpacUri_Stem + $database + (Get-Date -Format ddmmyy) + ".bacpac" $exportRequest = New-AzureRmSqlDatabaseExport -ResourceGroupName $ResourceGroupName -ServerName $ServerName ` -DatabaseName $Database -StorageKeytype $StorageKeytype -StorageKey $StorageKey -StorageUri $BacpacUri ` -AdministratorLogin $UserName -AdministratorLoginPassword $Password } 

Answer 2

This doesnt make a lot of sense, you can always restrict people from accessing resources in Azure. Also, deleting an Azure SQL database doesnt delete the backups if the server is not deleted as well. you can use Long Term Retention to backup to a different "media".