| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | |||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
managed_backup.fn_backup_db_config (Transact-SQL) |
managed_backup.fn_backup_db_config (Transact-SQL) |
MikeRayMSFT |
mikeray |
06/10/2016 |
sql |
system-objects |
reference |
|
|
|
[!INCLUDE sqlserver2016]
Returns 0, 1 or more rows with [!INCLUDEss-managed-backup] configuration settings. Returns 1 row for the specified database, or returns the information for all the databases configured with [!INCLUDEss-managed-backup] on the instance.
Use this stored procedure to review or determine the current [!INCLUDEss-managed-backup] configuration settings for a database or all the databases on an instance of SQL Server.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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 Name | Data Type | Description |
|---|---|---|
| db_name | SYSNAME | Database name. |
| db_guid | UNIQUEIDENTIFIER | Identifier that uniquely identifies the database. |
| is_availability_database | BIT | Whether 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_dropped | BIT | A value of 1 indicates that this is a dropped database. |
| credential_name | SYSNAME | Name of the SQL Credential used to authenticate to the storage account. NULL value indicates that no SQL Credential has been set. |
| retention_days | INT | The current retention period in days. NULL value indicates that [!INCLUDEss-managed-backup] was never configured for this database. |
| is_managed_backup_enabled | INT | Indicates whether [!INCLUDEss-managed-backup] is currently enabled for this database. A value of 1 indicates that [!INCLUDEss-managed-backup] is currently enabled, and a value of 0 indicates that [!INCLUDEss-managed-backup] is disabled for this database. |
| storage_url | NVARCHAR(1024) | The URL of the storage account. |
| Encryption_algorithm | NCHAR(20) | Returns the current encryption algorithm to use when encrypting the backup. |
| Encryptor_type | NCHAR(15) | Returns the encryptor setting: Certificate or Asymmetric Key. |
| Encryptor_name | NCHAR(max_length_of_cert/asymm_key_name) | The name of the certificate or asymmetric key. |
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 [!INCLUDEss-managed-backup] 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 [!INCLUDEss-managed-backup] 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)