Skip to content

Latest commit

 

History

History
69 lines (47 loc) · 4.59 KB

set-the-expiration-date-on-a-backup-sql-server.md

File metadata and controls

69 lines (47 loc) · 4.59 KB
Raw
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Set the expiration date on a backup (SQL Server)
This article shows you how to set the expiration date on a backup in SQL Server by using SQL Server Management Studio or Transact-SQL.
MashaMSFT
mathoma
randolphwest
04/25/2024
sql
backup-restore
conceptual
backing up databases [SQL Server], expiration dates
expiration [SQL Server]
database backups [SQL Server], expiration dates

Set the expiration date on a backup (SQL Server)

[!INCLUDE SQL Server]

This article describes how to set the expiration date on a backup in [!INCLUDE ssnoversion] by using [!INCLUDE ssManStudioFull] or [!INCLUDE tsql].

If you append multiple database backups in a single file, you can use the expiration date to avoid overwriting backups before that date. For more information, see Media set options.

Permissions

BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

Ownership and permission problems on the backup device's physical file can interfere with a backup operation. [!INCLUDE ssNoVersion] must be able to read and write to the device; the account under which the [!INCLUDE ssNoVersion] service runs must have write permissions. However, sp_addumpdevice, which adds an entry for a backup device in the system tables, doesn't check file access permissions. Such problems on the backup device's physical file might not appear until the physical resource is accessed when the backup or restore is attempted.

Use SQL Server Management Studio

  1. After connecting to the appropriate instance of the [!INCLUDE ssDEnoversion], in Object Explorer, select the server name to expand the server tree.

  2. Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.

  3. Right-click the database, point to Tasks, and then select Back Up. The Back Up Database dialog box appears.

  4. On the General page, for Backup set will expire, specify an expiration date to indicate when the backup set can be overwritten by another backup:

    • To have the backup set expire after a specific number of days, select After (the default option), and enter the number of days after set creation that the set will expire. This value can be from 0 to 99,999 days; a value of 0 days means that the backup set never expires.

      The default value is set in the Default backup media retention (in days) option of the Server Properties dialog box (Database Settings page). To access this value, right-click the server name in Object Explorer and select Properties; then select the Database Settings page.

    • To have the backup set expire on a specific date, select On, and enter the date on which the set expires.

Use Transact-SQL

  1. Connect to the [!INCLUDE ssDE].

  2. From the Standard bar, select New Query.

  3. In the BACKUP (Transact-SQL) statement, specify either the EXPIREDATE or RETAINDAYS option to determine when the [!INCLUDE ssDEnoversion] can overwrite the backup. If neither option is specified, the expiration date is determined by the media retention server configuration setting. This example uses the EXPIREDATE option to specify an expiration date of June 1, 2024 (20240601).

    USE AdventureWorks2022;
GO

BACKUP DATABASE AdventureWorks2022
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2022.bak'
WITH EXPIREDATE = '20240601';
GO

Related content