September 9, 2003 at 10:33 am
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
you can use "WITH SKIP" to ignore the expiration date.
September 10, 2003 at 7:37 am
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
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
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 10, 2003 at 10:00 am
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