March 2, 2009 at 1:14 am
Suppose that we are taking backups of a database into a Backup device (file located on disk), and during the BACKUP statement we use the EXPIREDATE option also.
But when I make another BACKUP statement (after the expiry time has been reached for a particular backup in the set), the expired backup doesnt get cleared.
If I use INIT then everything gets cleared. So, is there any other way this can be done, or does EXPIREDATE will work with tape backups only?
March 2, 2009 at 9:57 am
I would be very interested in this answer as well.
We are provided with single .bak files which contain many backups with in it on an ad-hoc basis.
It would be good to know how to go about removing some of these from the .bak (or even copying the required ones into a new file if the first option isn't available).
March 2, 2009 at 9:58 am
Haven't tested it, but this (http://msdn.microsoft.com/en-us/library/ms186865.aspx) seems to indicate it does.
It doesn't prevent another process or user from deleting them.
March 2, 2009 at 11:33 pm
I saw that syntax for BACKUP page in Books online also. Even there it is not clearly specified if the EXPIREDATE will work for disk backups or not. it is generally specified as "media". when I try with a dump device in my disk, it doesnt work. (the old backup file doesnt get deleted when I take new backups even though it has expired).
March 3, 2009 at 9:19 am
Noz (3/2/2009)
We are provided with single .bak files which contain many backups with in it on an ad-hoc basis.
Using one file is not a good thing becuase if that file is deleted or becomes corrupt, you lose everything. Separate files and then copy/move to a network share if disk is at a premium. It may save you a headache or two down the road - IMO.
-- You can't be late until you show up.
March 3, 2009 at 9:29 am
I've had this issue in the past with native backups. I found that xp_delete_file worked:
EXECUTE master.dbo.xp_delete_file
0, -- delete files
@path, -- full path to the main backup directory
'bak', -- file extension, can also be 'txt', for example, with a value of 1 in the first parameter (0 = backups, 1 = reports, IIRC)
@expiredate, -- delete files created before this timestamp
1 -- Including first-level subfolders if required
Of course, xp_delete_file is undocumented and may not be available in future releases. It does work in SQL 2005, though.
March 3, 2009 at 9:30 am
tosscrosby (3/3/2009)
Noz (3/2/2009)
We are provided with single .bak files which contain many backups with in it on an ad-hoc basis.Using one file is not a good thing becuase if that file is deleted or becomes corrupt, you lose everything. Separate files and then copy/move to a network share if disk is at a premium. It may save you a headache or two down the road - IMO.
Agree with everything you say tosscrosby, that's why I'm keen to find a solution to the .bak files we are provided with - it's not our choice.
March 3, 2009 at 10:22 am
Expiredate isn't a delete. It allows rewriting of the file. If you put multiple backups in ONE file. meaning you have sales.bak and it has backups for last night and two nights ago (you can do this), the expire date allows overwriting of the backup in the file. It doesn't delete files.
This is, IMHO, a bad practice.
You can set up a maintenance plan to delete old backups. Use the cleanup task.
March 4, 2009 at 12:56 am
Steve Jones - Editor (3/3/2009)
Expiredate isn't a delete. It allows rewriting of the file.
does it mean that it will overwrite the space used for the old expired backups with the new backups being taken?
i assume that it works with tape backups only..
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply