managed_backup.fn_backup_db_config (Transact-SQL)

 

Updated: June 10, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns 0, 1 or more rows with SQL Server Managed Backup to Microsoft Azure configuration settings. Returns 1 row for the specified database, or returns the information for all the databases configured with SQL Server Managed Backup to Microsoft Azure on the instance.

Use this stored procedure to review or determine the current SQL Server Managed Backup to Microsoft Azure configuration settings for a database or all the databases on an instance of SQL Server.

Topic link icon Transact-SQL Syntax Conventions

managed_backup.fn_backup_db_config (‘database_name’ | ‘’ | NULL)  

@db_name
The name of the database. The @db_name parameter is SYSNAME. If an empty string or NULL value is passed to this parameter, the information about all the databases on the instance of SQL Server is returned.

Column NameData TypeDescription
db_nameSYSNAMEDatabase name.
db_guidUNIQUEIDENTIFIERIdentifier that uniquely identifies the database.
is_availability_databaseBITWhether the database is participating in Availability Group. A value of 1 indicates that the database is an Availability database and 0 that it is not.
is_droppedBITA value of 1 indicates that this is a dropped database.
credential_nameSYSNAMEName of the SQL Credential used to authenticate to the storage account. NULL value indicates that no SQL Credential has been set.
retention_daysINTThe current retention period in days. NULL value indicates that SQL Server Managed Backup to Microsoft Azure was never configured for this database.
is_smart_backup_enabledINTIndicates whether SQL Server Managed Backup to Microsoft Azure is currently enabled for this database. A value of 1 indicates that SQL Server Managed Backup to Microsoft Azure is currently enabled, and a value of 0 indicates that SQL Server Managed Backup to Microsoft Azure is disabled for this database.
storage_urlNVARCHAR(1024)The URL of the storage account.
Encryption_algorithmNCHAR(20)Returns the current encryption algorithm to use when encrypting the backup.
Encryptor_typeNCHAR(15)Returns the encryptor setting: Certificate or Asymmetric Key.
Encryptor_nameNCHAR(max_length_of_cert/asymm_key_name)The name of the certificate or asymmetric key.

Permissions

Requires membership in the db_backupoperator database role with ALTER ANY CREDENTIAL permissions. The user should not be denied VIEW ANY DEFINITION permissions.

The following example returns the SQL Server Managed Backup to Microsoft Azure configuration for ‘TestDB’

For each code snippet, select 'tsql' in the language attribute field.

Use msdb  
GO  
SELECT * FROM managed_backup.fn_backup_db_config('TestDB')  

The following example returns the SQL Server Managed Backup to Microsoft Azure configuration for all the databases on the instance of SQL Server it is executed on.

Use msdb  
GO  
SELECT * FROM managed_backup.fn_backup_db_config (NULL)  

Community Additions

ADD
Show: