BACKUP EXPIREDATE/RETAINDAYS Question

Question

BACKUP EXPIREDATE/RETAINDAYS Question

Sam Sheikh

Say Hey Kid

Points: 678
More actions
April 20, 2006 at 4:33 am

#167849

Hello,
I have two general BACKUP questions, I hope someone will be able to help me with:
1. What is the difference between EXPIREDATE and RETAINDAYS when using BACKUP?
2. What process actually deletes a backup file when it reaches it's expiration date? If the backup file were to be copied to another file system on another server, would it still get deleted on expiration?
Thanks in advance for any help.
Sam

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Norene Malaney SSCommitted Points: 1896 More actions · Answer 1

Found the following at http://msdn2.microsoft.com/en-US/library/ms186865(SQL.90).aspx. It seems to be that the difference is that "EXPIREDATE" gives you the option of specifiying the ACTUAL DATE the backup can be overwritten while "RETAINDAYS" allows you the option of specifying the NUMBER OF DAYS before the backup can be overwritten.

Note the "Important Note" below. Using these options only prevents SQL Server from overwriting these files.

Hope this helps.

EXPIREDATE = { date | @date_var }

Specifies when the backup set expires and can be overwritten. If supplied as a variable (@date_var), this date must follow the configured system datetime format and be specified as one of the following:

A string constant (@date_var = date)
A variable of character string data type (except for the ntext or text data types)
A smalldatetime
A datetime variable

RETAINDAYS = { days | @days_var }

Specifies the number of days that must elapse before this backup media set can be overwritten. If supplied as a variable (@days_var), it must be specified as an integer.

Important:
If EXPIREDATE or RETAINDAYS is not specified, expiration is determined by the media retention configuration setting of sp_configure. These options only prevent SQL Server from overwriting a file. Tapes can be erased using other methods, and disk files can be deleted through the operating system. For more information about expiration verification, see SKIP and FORMAT in this topic.

Have a good day,

Norene Malaney

Sam Sheikh Say Hey Kid Points: 678 More actions · Answer 2

Thanks for you help Norene. I think I maybe on the wrong wave length with using EXPIREDATE/RETAINDAYS. What I was trying to acheive was the deletion of the backup file after a set interval. Basically the option available in the maintenence plan generator for database backups, i.e "Remove Files Older Than" option.

Would you or anyone else know how this is achived and whether I could use it?

Norene Malaney SSCommitted Points: 1896 More actions · Answer 3

I use the "Remove Files Older Than" option in my maintenance plans. For example, my maintenance plan #1 writes all backups to C:\Backups and runs every Sunday. Maintenance plan #1 has the "Remove Files Older than" option set to " 4 weeks". Every Sunday when maintenance plan #1 runs, one of the tasks it performs is to check if any of the files in C:\Backups are older than 4 weeks, if so, it deletes these backup files.

Hope this helps.

Have a good day,

Norene Malaney