August 4, 2006 at 11:01 am
Hi, all!
I've got a little issue. I have a 2 SQL 2000 databases on the same server which are using a maintenance plan for their log and database backups. The backups are writing to a NAS. There are no problems or errors with the maintenance plans and the backups are being deleted as they are supposed to.
The problem comes when I created a separate job to do the reindexing (not using the maintenance plan). In order to keep it from making a huge log file, I setup the job to change the recovery mode to simple during the reindexing. Before the change to simple I do a transaction log backup, and I do a full backup after the change back to full recovery. So this weekly reindexing creates two backup files.
I found out, though, that the maintenance plan is not including these .bak and .trn files when it deletes old files. They are in the same directory and the jobs are all running under the same permissions. Again, the maintenance plan is successfully deleting the backups it created but not those created outside of the plan. So it's not a permissions or access problem.
Do you know if it is possible to for a maintenance plan to delete manual backups? Since they are all being recorded in MSDB, I would think that it wouldn't discriminate. Perhaps it doesn't just query backupset and backupmedia family like I would expect it does. Any ideas? The only thing I can think of is that they are named differently and the bak/trn extensions are in different case.
Thanks!
August 4, 2006 at 11:48 am
You might be able to apply some of the same principles I used in my article to your situation:
http://www.sqlservercentral.com/columnists/aingold/workingaround2005maintenanceplans.asp
I'd say capture all the backup file names into a table and then have a job which regularily goes through and cleans them up after a specific expiration date.
August 4, 2006 at 11:50 am
Hi, Brian.
I'm not sure if xp_delete_file existed in SQL 2K, but I used it in SQL 2005 as a workaround to a similar bug in the newer version. Just add a step to your plan that executes this procedure with a file extension, path and a date.
Here is a sample to delete files 7 days and older:
Declare @delDate datetime
select @delDate = convert(nvarchar, dateadd(dd, -7, getdate()),101)
EXECUTE master.dbo.xp_delete_file 0,N'D:\folderpath',N'bak', @delDate
Hope this helps.
Eddie
"If you don't take the time to do it right, when are you going to find the time to do it over?"
August 4, 2006 at 11:54 am
Thanks for the feedback! I already have a stored procedure that I use throughout our enterprise for removing old backup files. I was just hoping that I wouldn't have to go through the bother of setting it up. I was hoping that the already-existing maintenance plan would be able to take care of it. Well, I might just have to use that proc afterall.
Thanks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply