How to check backup retention period of a database via TSQL query

Question

Hi,

I want to check the backup retention period of an already configured backup set in SQL server

How can i check using TSQL query

Thanks

Answer 1

Hi @Sharon01

want to check the retention period , or how long the backup file will stay on he media , before it gets deleted

How about this query:

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
       msdb.dbo.backupset.database_name, 
       msdb.dbo.backupset.expiration_date,
	   DATEDIFF(DAY,GETDATE(),msdb.dbo.backupset.expiration_date) AS Retention_Days,
       CASE msdb..backupset.type WHEN 'D' THEN 'Database' 
	                             WHEN 'L' THEN 'Log' END AS backup_type, 
       msdb.dbo.backupset.backup_size, 
       msdb.dbo.backupset.name AS backupset_name, 
       msdb.dbo.backupset.description 
FROM msdb.dbo.backupset 
ORDER BY msdb.dbo.backupset.database_name,msdb.dbo.backupset.backup_finish_date 

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi Sharon,

You may check in the maintenance job tasks , how many weeks or days are configured to delete files like shown in below snapshot (files older than 4 weeks will be deleted):

Hope this will help.

Answer 3

SELECT
    name AS DatabaseName,
    recovery_model_desc AS RecoveryModel,
    backup_set_id AS BackupSetID,
    backup_start_date AS BackupStartDate,
    backup_finish_date AS BackupFinishDate,
    expiration_date AS ExpirationDate
FROM msdb.dbo.backupset
WHERE database_name = 'YourDatabaseName' -- Replace with your database name
ORDER BY backup_start_date DESC;

Answer 4

If you mean the "expiration date", you get it with

https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/backupset-transact-sql?view=sql-server-ver16

select * from msdb.dbo.backupset

Note: That's only meta data and has no functionallity.

Answer 5

Hi Sharon,

What exactly do want to receive as a resultset?

The first question would be, how do you back up your database? With a third-party tool like Veeam or native TSQL in an AgentJob or as a MaintenanceJob?

There are several options for a resultset... on a backupset there is no "retention time" just a "time until overwrite" or a "time to live" which in the end is like a retention period... but may be different approaches to get that time...

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!