Partager via

Extended “Backup” Retention with Azure SQL Database

  • Article

As you may or may not know, Azure SQL Database is a great tiered offering that comes with backup retention capabilities out of the box.  What is awesome about this offering is it makes backing up your database braindead simple, and saves you the administrative effort of creating a maintenance plan to do this.  It also saves you the hassle of planning out the nuances of full backups, delta backups, and log backups necessary in order to achieve your recovery goals.  Finally it saves you the headache of remembering to delete aged backup files.  After all, nobody wants to find out a backup failed because you ran out of disk space.

As I mentioned before, this is a tiered offering and the out of the box backup capabilities are no exception to this.  How much backup retention you get depends entirely on what tier of our database offering you choose.  At the time of this post the retention is as follows:

  • Premium (P1 – P15) and Standard (S0 – S3) Databases – 35 days of Point in Time Restore
  • Basic Database (B) – 7 days of Point in Time Restore

A question I’m often asked is whether this feature is configurable on a per database basis.  Most customers I speak with would gladly pay the extra storage costs to make this a tuneable feature on the database, and add the option of sacrificing the Point in Time nature of the restore as the backups age out.  Another question I’m asked is how to best take a preemptive backup before a deployment so there is a known good state to recover to in the event of a deployment failure.   The built in backup can aid in the deployment scenario just fine, but the challenge is often understanding what “point in time” to recover to.  Those responsible for a deployment rest easier knowing there is a snapshot of the database when all else fails.

While these features (tuneable retention & on demand backup) aren’t addressed as part of the built in backup capabilities of Azure SQL Database there is a way we can use other features of the database in order to achieve this namely SQL Database Copy and SQL Database Export.  While not technically “backup” these features can be used to get you a consistent copy of your database for disaster purposes, hence the quotes.  (NOTE: When I put the quotes there I have a mental image of Mike Myers aka Dr. Evil doing air quotes.)

Let’s explore these two features a bit…

SQL Database Copy – is designed to make a transactionally consistent copy of your database leveraging the built in backup retention policy.  It first makes a copy of the tail of the transaction log, and then uses this tail plus the full, differential, and log backups retained behind the scenes in Azure SQL Database.  All this is done without interrupting the log chain of the current database retention plan (a common problem with preemptive full backups in on premises databases).

SQL Database Export – is designed to export a copy of your database to a .bacpac file which is an encapsulated version of the databases schema and data.  While not truly a backup file like a .bak file in a traditional SQL database, it does contain everything you need to reconstitute your database in the event of an emergency.

Based on these two descriptions, you might ask yourself “why do I need both?”.  This is the point where the fine print is important.  There are two reasons I think it’s essential to use both of these features in concert:

  1. SQL Database Export is not transactionally consistent. Therefore, it is strongly recommended that you have a database that is not being actively used while you take the copy.  Hence I would recommend starting with database copy, then export, and ultimately drop the copied database.
  2. SQL Database Copy creates a copy of your database. Therefore, if you have a 50 GB P1 database after the operation you’ll have two 50GB P1 databases, and consequently double the cost of hosting the database.  Each time you run this operation you’re increasing your cost, and each database gets its own retention schedule which is unnecessary.  Using the bacpac approach you keep the database copy around for a small amount of time and keep your costs low.  You also get a choice whether you want this retention in Azure Storage which is much cheaper than Azure SQL to use or to take the bacpac out of Azure and into your own data center.

Now that we have a good background on why I’m recommending this approach, here are a few powershell script segments to get you started.  I logically break this down into three discrete steps:

  1. Initialize variables.
    # Define the source datbase, server, and resoruce group $sourceDatabase = "[source database]" $sourceServer = "[source server]" $sourceResourceGroup = "[source resource group] # Define output location for bacpac $storageAccount = "[blob account].blob.core.windows.net" $storageContainer = "[blob container]" $storageKeyType = "StorageAccessKey" $storageKey = "[storage key]" # Admin credentials for the database $adminUser = "[admin user name]" $adminPassword = "[admin password]" # Generating a unique name for the databse copy and the uri for the bacpac. $destinationDatabase = $sourceDatabase + (Get-Date).ToString("yyyyMMddHHmm") $bacpacUri = "https://" + $storageAccount + "/" + $storageContainer + "/" + $destinationDatabase + ".bacpac"
  2. Create the database copy. # Execute the copy command New-AzureRmSqlDatabaseCopy -ResourceGroupName $sourceResourceGroup ` -ServerName $sourceServer ` -DatabaseName $sourceDatabase ` -CopyDatabaseName $destinationDatabase
  3. Export the bacpac to the storage account. # Note:  Exporting to a bacpac requires you to have an account and password for an admin. #        If you do not include this the command will ask for it at execution time. $exportRequest = New-AzureRmSqlDatabaseExport   –ResourceGroupName $sourceResourceGroup ` –ServerName $sourceServer ` –DatabaseName $destinationDatabase ` –StorageKey $storageKey ` -StorageUri $BacpacUri ` –StorageKeytype $storageKeyType ` –AdministratorLogin $adminUser ` –AdministratorLoginPassword $adminPassword
  4. Cleanup the database copy. #Now that the export is done, delete the databse. Remove-AzureRmSqlDatabase  -ResourceGroupName $sourceResourceGroup ` -ServerName $sourceServer ` -DatabaseName $destinationDatabase

This is the gist of it.  In order to save yourself some headaches with copy and paste, and for a complete version of the powershell script checkout my github repository @ For a complete script you can check out my github repository