September 12, 2008 at 3:34 pm
Hello,
I have a T-SQL command I use to backup transaction logs to a device. This runs every 1/2 hour but I only want it to retain a days work at a time. But I always want a days worth in there so 24 x 2 = 48 logs at any given time. I am having trouble figuring out how to delete the log backs that are older than a day in there without clearing out the whole device. Any help would be greatly appreciated! (I thought the retaindays below would do it)
BACKUP LOG [EXAMPLEDATABASE] TO DISK = N'\\ma-atl85\sql\sqlbackup\ma-atl79\EXAMPLEDATABASE_Transaction_Backups_24Hours.trn'
WITH RETAINDAYS = 1, NOFORMAT, NOINIT, NAME = N'Netsteps Transaction Log Backups',
NOSKIP, NOREWIND, NOUNLOAD, STATS = 10, BLOCKSIZE = 4096
GO
Thank you kindly!
GP
September 12, 2008 at 3:51 pm
Instead of RETAINDAYS, try EXPIREDATE=dateadd(hh, 25, getdate()).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 15, 2008 at 12:09 pm
Hello RBarryYoung,
Thanks for your reply. I tested that and some other things and am still having no luck. I'm starting to think there is no way. If I set the backup sets to expire after a day, the expire date actually does get set but they do not get removed from the device. If I try to run it with the INIT I get an error that some of the backup sets have not yet expired and the whole statement fails. If I run it with the SKIP it ignores the error but deletes all files in the device so I don't retain the day's worth.
I read one article that said to use RETAINDAYS and INIT together. I cleared the device and tried that but has the same results. After the first backup the statement will start failing because it says the backup set has not yet expired.
Msg 4030, Level 16, State 1, Line 4
The medium on device '\\ma-atl85\sql\sqlbackup\ma-atl79\Netsteps_Transaction_Backups_24Hours.trn' expires on Sep 16 2008 1:58:18:000PM and cannot be overwritten.
Msg 3013, Level 16, State 1, Line 4
BACKUP LOG is terminating abnormally.
If anyone has any more suggestions I really appreciate it. Otherwise I might just throw in the towel and go back to individual file backups (which just gets ugly to manage)
Thanks!!
GP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply