Azure SQL Database Pause Resume
| |

Pause / Resume / Backup an Azure SQL Database with Powershell – Part 3

This post might contain affiliate links. We may earn a commission if you click and make a purchase. Your support is appreciated!

Many services in Azure allow certain cost savings through automation, this can also be achieved with the Platform-as-a-Service “Azure SQL Database”, this is what this blog post is all about. Quite as simple as e.g. with the Azure Analysis Service it is not because actually there is no pause-resume functionality – here comes the backup into place, that’s what I’ll talk about first.

Azure SQL Database and the Backup topic

Before we dive deeper into the Azure SQL Database and its pause-resume functionality, we first have to take a brief look at the topic of backup, which is not quite unimportant in this context. A big advantage of Azure SQL Database against a SQL Server (on premise or Iaas) in any case is … you do not have to explicitly worry about the backup since this is created automatically. According to the database size and number of data changes, Microsoft automatically backs up the databases at regular intervals! That means that the costs for an extra backup software or even the development of own services are already gone. Depending on Performance-Level, there are different backup retention times:

  • at the basic level the retention time is 7 days.
  • at the standard level the retention time is 35 days.
  • at the premium level the retention time is 35 days.

When will my database be backed up? Also there is an answer …
The full backup takes place every week (unfortunately) at non-fixed times, differential backups are generally created every few hours and depending on the data volume, the transaction logs are saved every 5-10 minutes. A first full backup is created within the first 30 minutes after the database is created. These backups are kept according to the service tier(see above) (cost savings: you do not need an extra storage account because the backup is already included in the price). If you want to keep your backup for more than 35 days, you have the option of activating a “Long-Time Retention-Backup”. This requires a further storage account, where the backups are stored in parallel and permanently.

Pause and Resume for cost savings

This functionality is unfortunately not available with Azure SQL Database … How can I still benefit with cost savings if I would like to use this platform-as-a-service… Of course, as already explained in another blog post changing the database performance to prevent occurring load peaks. But we would really like to achieve certain cost savings with the migration to the cloud … if a department only works during the day (8am-8pm), then I need this database(s) only during the night… So why you cannot stop those services, since you only pay when the database is online?

For this scenario, the department needs the database only during the day, there is actually no solution, but a workaround, dropping the database in the evening and restoring the next morning via “Create Database from Backup” helps. This procedure has been implemented by Microsoft extremely pleasantly and does not mean a lot of effort.

# Dropping DB to stop costs
Get-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename -ev notPresent -ea 0
if ($notPresent) {
    Write-Host $databasename "already deleted" 
} else {
    Remove-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename
}

Please note that you only delete/remove the database and not the logical server, because the backup history is stored on that logical SQL server. In order to restore this database, you’ll need the date of the last backup database. When you restore the database on the following morning, you’ll directly use this backup time to perform a restore. In Powershell, you can combine these activities very easily.

$deleteddatabase = Get-AzureRmSqlDeletedDatabaseBackup -ResourceGroupName $resourcegroupname -ServerName $servername #-DatabaseName $databasename
$deleteddatabase
# Do not continue until the cmdlet returns information about the deleted database.

Restore-AzureRmSqlDatabase -FromDeletedDatabaseBackup `
    -ResourceGroupName $resourcegroupname `
    -ServerName $servername `
    -TargetDatabaseName $databasename `
    -ResourceId $deleteddatabase.ResourceID `
    -DeletionDate $deleteddatabase.DeletionDate `
    -Edition "Standard" `
    -ServiceObjectiveName "S0"

You can find more sample scripts on backup / restore here => https://docs.microsoft.com/de-de/azure/sql-database/scripts/sql-database-restore-database-powershell

This post might contain affiliate links. We may earn a commission if you click and make a purchase. Your support is appreciated!

Similar Posts

2 Comments

  1. Hi Björn! Is there any way to get sql azure db backup history? I’m getting last available backup from get-azureRMSqlDatabaseGepBackup cmdlet but we need to get the historical instead of the last one

    1. Hi Lucas,

      as far as I know (and tried): No, you won’t get a detailed list of available backups.
      There is no need for that detailed list (at least from the Microsoft point of view) as you are able to restore that database to every point-in-time you wish so why do you want to have a detailed list of all backups.
      Just try and test if you are able to restore your database to a given point-in-time. 😉
      Sorry, but I can’t give another answer.

      You might also try this command:
      Get-AzureRmSqlDatabaseRestorePoints -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename

      But there is still no command to retrieve a detailed list…
      As you might already know => MSFT Docs for Restores

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.