| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | |||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
managed_backup.fn_available_backups (Transact-SQL) |
managed_backup.fn_available_backups (Transact-SQL) |
MikeRayMSFT |
mikeray |
03/14/2017 |
sql |
system-objects |
reference |
|
|
|
[!INCLUDE sqlserver2016]
Returns a table of 0, one or more rows of the available backup files for the specified database. The backup files returned are backups created by [!INCLUDEss-managed-backup].
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
managed_backup.fn_available_backups ([@database_name = ] 'database name') @database_name
The name of the database. The @database_name is NVARCHAR(512).
The table has a unique clustered constraint on (database_guid, backup_start_date, and first_lsn, backup_type).
If a database is dropped and then recreated, the backup sets for all the databases are returned. The output is ordered by the database_guid, which uniquely identified each database.
If there are gaps in LSN meaning that there is a break in the log chain, the table will contain a special row for each missing LSN segment.
| Column name | Data type | Description |
|---|---|---|
| Backup_path | NVARCHAR(260) COLLATE Latin1_General_CI_AS_KS_WS | The URL of the backup file. |
| backup_type | NVARCHAR(6) | 'DB' for database backup 'LOG' for log backup |
| expiration_date | DATETIME | The date on which this file is expected to be deleted. This is set based on the ability to recover the database to a point in time within the specified retention period. |
| database_guid | UNIQUEIDENTIFIER | The GUID value for the specified database. The GUID uniquely identifies a database. |
| first_lsn | NUMERIC(25, 0) | Log sequence number of the first or oldest log record in the backup set. Can be NULL. |
| last_lsn | NUMERIC(25, 0) | Log sequence number of the next log record after the backup set. Can be NULL. |
| backup_start_date | DATETIME | Date and time the backup operation started. |
| backup_finish_date | NVARCHAR(128) | Date and time the backup operation finished. |
| machine_name | NVARCHAR(128) | Name of the computer where the SQL Server instance is installed and running [!INCLUDEss-managed-backup]. |
| last_recovery_fork_id | UNIQUEIDENTIFIER | Identification number for the ending recovery fork. |
| first_recovery_fork_id | UNIQUEIDENTIFIER | ID of the starting recovery fork. For data backups, first_recovery_fork_guid equals last_recovery_fork_guid. |
| fork_point_lsn | NUMERIC(25, 0) | If first_recovery_fork_id is not equal to last_recovery_fork_id, this is the log sequence number of the fork point. Otherwise, this value is NULL. |
| availability_group_guid | UNIQUEIDENTIFIER | If a database is an Always On database, this is the GUID of the availability group. Otherwise this value is NULL. |
0 (success) or 1 (failure).
Requires SELECT permissions on this function.
The following example lists all the available backups backed up through [!INCLUDEss-managed-backup] for the database 'MyDB'
SELECT *
FROM msdb.managed_backup.fn_available_backups ('MyDB')
SQL Server Managed Backup to Microsoft Azure
Restoring From Backups Stored in Microsoft Azure