April 21, 2006 at 2:19 am
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
April 21, 2006 at 11:05 pm
Hi Dear,
Ans 1: When we set the overwrite exisiting Media then only wee can set the backup expire date.
The expirate date is valid only for backup data stored on disk or tape devices.Backup set older then expire date can be overwritten by later backup.
RetainDays
Use the media retention option to provide a system-wide default for the length of time to retain each backup medium after it has been used for a database or transaction log backup. media retention helps protect backups from being overwritten until the specified number of days has elapsed. When you set media retention, you do not have to specify the length of time to retain system backups each time you perform a backup. The default is 0 days. If you use the backup medium before the set number of days has passed, Microsoft® SQL Server™ issues a warning message. SQL Server does not issue a warning unless you change the default.
This option can be overridden by using the RETAINDAYS clause of the BACKUP statement.
media retention is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change media retention only when show advanced options is set to 1. The setting takes effect after stopping and restarting the server.
Ans:In backup set table the backup expire date is stored if u set the date.When u copy the datebase backup file to another server it will be not be deleted becasue the file will be deleted from the server on which backup has been performed.
How the backuo files are removed i dont remember.But u can delete the files using xp_cmdshell.
HTH
from
Killer
April 24, 2006 at 2:42 am
Thanks Mr Killer,
I think I maybe on the wrong wave length with using EXPIREDATE/RETAINDAYS. What I was trying to achieve 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. I'm trying to write a SP to do a local DB dump, copy it over to a remote system and load it using RPC. I want to use a "MS" type naming convention i.e. DB name followed by date and time. I would like the backup set held for some time before deletion and because of the naming convention I will not nessaraily know the file name.
Thanks for any light you or anyone else can throw on this subject.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply