Search code examples
sql-serverazure-sql-databaseazure-bicep

Create a SQL Server/database backup via bicep?

I have the following bicep to create a SQL Server and SQL Server database.

resource sqlServer 'Microsoft.Sql/servers@2021-02-01-preview' = {
  name: sqlServerName
  location: location
  identity: {
    type: 'SystemAssigned'
  }
  properties: {}

  resource sqlDataBase 'databases@2021-02-01-preview' = {
    name: sqlServerName
    location: location
    sku: {
      name: 'GP_S_Gen5'
      tier: 'GeneralPurpose'
      family: 'Gen5'
      capacity: 4
    }
  }

Is it possible to create a SQL Server/database backup via bicep?

UPDATE:

https://learn.microsoft.com/en-us/azure/templates/microsoft.sql/servers/databases/backuplongtermretentionpolicies?pivots=deployment-language-bicep

What each of these indicates: monthlyRetention, weeklyRetention, weekOfYear, yearlyRetention with some example

Solution

  • Thank you @Thomas for your comments.

    I used below code from This MS Document1 and this MS Document2 to deploy Azure SQL Server with Backup:-

    main.bicep:-

    
param  serverName  string = uniqueString('sql', resourceGroup().id)

  

@description('The name of the SQL Database.')

param  sqlDBName  string = 'SampleDB'

  

@description('Location for all resources.')

param  location  string = resourceGroup().location

  

@description('The administrator username of the SQL logical server.')

param  administratorLogin  string

  

@description('The administrator password of the SQL logical server.')

@secure()

param  administratorLoginPassword  string

  

resource  sqlServer  'Microsoft.Sql/servers@2022-05-01-preview' = {

name: serverName

location: location

properties: {

administratorLogin: administratorLogin

administratorLoginPassword: administratorLoginPassword

}

}

  

resource  sqlDB  'Microsoft.Sql/servers/databases@2022-05-01-preview'
= {

parent: sqlServer

name: sqlDBName

location: location

sku: {

name: 'Standard'

tier: 'Standard'

}

}

  

resource  backupPolicy 
'Microsoft.Sql/servers/databases/backupLongTermRetentionPolicies@2022-05-01-preview'
= {

parent: sqlDB

name: 'default'

properties: {

monthlyRetention: 'P1M'

weeklyRetention: 'P1W'

weekOfYear: 1

yearlyRetention: 'P1Y'

}

}

    Output:-

    enter image description here

    enter image description here

    What each of these indicates: monthlyRetention, weeklyRetention, weekOfYear, yearlyRetention with some example

    LTR- Is a long term retention or long term storage of your Azure DB backup in Azure blob storage.

    PTIR- Is a point in time restore, Which refers to the backup taken at a particular time and you can use the same backup later when your data is lost. You can configure how much days you want to keep your PTIR copy by creating a policy above where the PTIR is set to 7 Days.

    The above Bicep code sets Point in time restore days to 7, Which means you can restore your PTIR backups within 7 days.
    And how frequently I want to take a backup is set by Differential backup frequency which is set to 24 hours.

    PTIR Reference is the same MS Document shared by @Thomas in comments.

    Now, Coming to LTR that is Long term retention policy - Weekly LTR is keeping the LTR backup to storage for a week which is set to 1 week. Monthly LTR is keeping the LTR backup to storage for a month which is set to 1. And Yearly LTR is keeping an LTR backup to storage for a year which is set to 1st week of 1 year. If you want to understand LTR in more detail, Refer this MS Document1