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?

Which specific kind of Azure database are you talking about: Azure SQL DB, Azure SQL DB Managed Instances, or SQL Server running inside a VM? — Brent Ozar, Dec 04 '18 at 01:13

score 1 · Accepted Answer · answered Jan 04 '19 at 16:09

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
}

Exactly what I needed. However this does not seem to backup the schema (tables). Is that a correct behavior? — judehall, Jan 12 '19 at 19:52

score 0 · Answer 2 · answered Dec 04 '18 at 05:53

0

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

answered Dec 04 '18 at 05:53

4c74356b41

628
5
10

My question mentions delete servers. – judehall Dec 04 '18 at 12:00
my answers mentions that as well – 4c74356b41 Dec 04 '18 at 12:02
Let's say somebody gets access to my master login and deletes everything. That is the scenario I would like to have a backup plan for. – judehall Dec 04 '18 at 12:03
you could use jit access and MFA so it becomes virtually impossible. in case you are really paranoid you can only do bacpac exportd – 4c74356b41 Dec 04 '18 at 12:04
Bacpac I probably what i'm looking for. Do you know of any convenient way to automate bacpac export in Azure. Or is it something i'd have to write a script for. – judehall Dec 04 '18 at 12:06
well, the question makes little sense, if you want to automate something you create a script ;) but there is a `New-AzureRmSqlDatabaseExport` cmdlet that does all the "heavy lifting" for you – 4c74356b41 Dec 04 '18 at 12:14
Thank you :) I'll look into New-AzureRmSqlDatabaseExport – judehall Dec 04 '18 at 12:16
you should consider upvoting\accepting this answer – 4c74356b41 Dec 04 '18 at 12:19

Convenient way to dump Azure SQL DB database

2 Answers2