27

While backing up the database, I would like to use "Backup set will Expire:" option to delete/overwrite old database backups. I am not able to use this option.

Any advice on how to use "Backup set will Expire:" options while creating Maintenance plan will be appreciated.

Improve this question
3
  • What does "I am not able to use this option" mean? Where did you get the idea that expiration will delete backups?
    – Aaron Bertrand
    Jun 20, 2014 at 11:53
  • Was this ever solved? I have a Maintenance cleanup task which only updates the msdb tables. It does not delete the physical backup files.
    – user56616
    Jan 14, 2015 at 18:27
  • @David Is it possible that the SQL Server Agent account does not have permission to delete those files? Are they local or remote? Have you searched at all? e.g. I found this, this and this on the first page of a search.
    – Aaron Bertrand
    Jan 14, 2015 at 20:09

4 Answers 4

Reset to default
28

The "Backup set will Expire" option is used to indicate how long a backup should be prohibited from being over written. It will not clean up old backup files. You can use the "Maintenance Clean up Task" in your maintenance plan to clean up backup files older than a certain number of days.

Improve this answer
2

From MSDN Back Up Database Task (Maintenance Plan)

Backup set will expire

Specify when the backup set can be overwritten by another backup set.

So, there's no deleting of old backups, just overwriting with new ones.

Any advice on how to use "Backup set will Expire:" options while creating Maintenance plan will be appreciated.

This depends on how long you want to keep your old backups. Does your company have a policy for that? Do you move your backups to a storage after a while? If you do, you can specify any number of days for expiry, as long as you copy the backups before the expiry time comes.

Improve this answer
2
  • Okay let me be more precise… One of my previous DBA designed (only on 1 server) maintenance plan with this option (Backup set will expire after 7 days) without adding Maintenance cleanup task, backup will be taken into a folder on the same Server. I end up manually deleting old backup files when there is a space crunch. Before I add Maintenance cleanup task to delete older backup files (older than 7 days), I am interested to understand my previous DBA intention to select this option rather than adding Maintenance cleanup task.
    – Vijred
    Jun 22, 2014 at 1:30
  • Thx for the clarification. It seems to me that he thought that the new backups will immediately overwrite the expired ones, but that doesn't happen.If you're in a space crunch, the best option is to use Maintenance cleanup tasks.
    – Milena Petrovic
    Jun 23, 2014 at 11:15
1

We had some problem with tape backups in one of the environments and started Archive bits to make sure that the backup files are not deleted before getting archived. Once the database is backed up in tape Archive bit will be updated and this bit is used to avoid deleting file before tape backups.

Improve this answer
-1

The "Maintenance Clean up Task" checked only file physically write time and not check the Backupset Expire time. This can use only with INIT Option.

Improve this answer
1
  • 1
    Sorry, but your comment makes no sense grammatically and also seems to add nothing to the accepted answer.
    – Colin 't Hart
    Jul 10, 2020 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.