Post reply

Expiration Date/ Retain Days

  • September 9, 2003 at 10:33 am

    #162353

    I have on single backup device for a transaction log backup.

    Is there a way to overwrite the backups in the device after a certain amount of time or specified date. I am toying with expiration date and can't seem to get the older backups to be overwritten even though they have already expired.

    Thanks,

    jeff


    "Keep Your Stick On the Ice" ..Red Green

  • September 9, 2003 at 3:50 pm

    #473324

    you can use "WITH SKIP" to ignore the expiration date.

  • September 10, 2003 at 7:37 am

    #473418

    Here's the problem:

    You can't overwrite just a part of the file, it has to be the whole thing. But, everytime you append a backup to the backup file the date gets updated.

    So this is what's happening:

    MYDBbackup.bak

    backup date added

    1stBackup 9/1/2003

    2ndBackup 9/2/2003

    3rdBackup 9/3/2003

    Let's say you have expiration set for 2 days. You do 1stBackup and the file "MYDBbackup.bak" is set to expire on 9/3, but when the 2ndBackup is added, the file "MYDBbackup.bak" now has a date of 9/2 and 2 days from that is 9/4; then the 3rdBackup is appended and the file now has a date of 9/3 with expiration of 9/5. The file can never be expired because the date keeps changing.

    If you want to "expire" a file, you need to do your backups to separate files. Such as:

    MonBackup, TuesBackup, etc......

    -SQLBill

  • September 10, 2003 at 9:58 am

    #473485

    I recommend that anyway. If you have corruption in the file you could lose a whole bunch of backups.

    If you really want them to be the same name, rename the previous one before running the next backup.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

    http://www.dkranch.net

  • September 10, 2003 at 10:00 am

    #473486

    Thanks for the input everyone!! I have come up with a solution that I feel will work good for us.

    I will INIT-ialize the transaction log backup device(file) if the last backup was a successful Full backup, otherewise append the backup in the file.

    I am trying to implement a backup strategy with EMC and bcv's..

    The main query driving my backups will tell me what was the last type of backup for that database...

    SELECT @LastBackupType = bs.type

    FROM msdb.dbo.backupset bs

    WHERE bs.database_name = @DatabaseName

    AND bs.backup_finish_date = (SELECT MAX(bs1.backup_finish_date)

    FROM msdb.dbo.backupset bs1

    WHERE bs1.database_name = bs.database_name)


    "Keep Your Stick On the Ice" ..Red Green

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

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