Maintenance plan to clean *.bak files older than 14 days - SQL 2005

Question

Maintenance plan to clean *.bak files older than 14 days - SQL 2005

arie01

Old Hand

Points: 336
More actions
January 14, 2013 at 6:35 am

#287591

Hello,
I'm new to DBAs and I have a fairly simple question for you experts.
I have a SQL 2005 server that hosts a few databases. Each database is backed to its own folder on the same server's hard drives. The path is E:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ then DB1, DB2 ... DB6.
I set a maintenance plan (using the wizard) to clean older *.bak files but it doesn't seem to work. So every now and then, I have to clean them manually to conserve disk space.
Could someone please help me set that up so it deletes backup files that are 15 days older than the current date?
Thank you,

Viewing 9 posts - 1 through 8 (of 8 total)

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

SQLmountain SSCommitted Points: 1505 More actions · Answer 1

Check the file extension you set up for the maintenance cleanup task. It needs to be set as "bak" without the dot (.bak).

Ant-Green SSC Guru Points: 113445 More actions · Answer 2

Time plays a big factor in the deletion as it looks for anything which is directly 15 days or older.

For example, day 1 backup finishes at 00:10, day16 finishes at 00:19, the first days backup us older than 15 days, so it will delete

Now in this example, day1 finishes at 00:10, day 16 finishes at 00:05, the first days backup is not older than 15 days, so it doesnt delete the file.

It even goes down to the second, so 00:10:00 and 00:10:01 deletes, 00:10:00 and 00:09:59 doesnt delete.

But from that, are you seeing files which are say 16, 17 days old still in the folder?

Are the files timestamped or are they constantly being backed up into 1 big backup file?

Also is the maintenance plan clean up task set to loop through sub-directories so that it can go into the individual databases folders to delete the old backups?

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 3

Check the file extension you set up for the maintenance cleanup task. It needs to be set as "bak" without the dot (.bak).

+1

Also, ensure that the "Include first level subfolders" checkbox is checked if all your db backups are in their own directories.

Joie Andrew
"Since 1982"

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Also, iirc, if the files are being backed up to another location by another process and are locked by that process the delete will not delete the file and it will remain in the dircetory. The delete older file process will then miss the file in subsequent runs as it will believe the file to already be deleted.

Now, this behavior may have been corrected, but it is what I saw happen in earlier versions of SQL Server.

arie01 Old Hand Points: 336 More actions · Answer 5

Hello,

Thank you for all your replies.

Using the wizard does not give me all these options. It doesn't give me an option to select which file types to delete .bak or bak, it doesn't give me an option to select include subdirectories or anything thing really.

Here is the T-SQL code after finishing with the wizard:

declare @dt datetime select @dt = cast(N'12/31/2012 09:05:40' as datetime) exec msdb.dbo.sp_delete_backuphistory @dt

GO

EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='12/31/2012 09:05:40'

GO

EXECUTE msdb..sp_maintplan_delete_log null,null,'12/31/2012 09:05:40'

If that code leaves me with one backup file less or extra, it would not be the end fo the world for me, so time is not so critical in my case as someone said.

Any ideas how to properly modifying the code so it would work properly?

Thank you,

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 6

Edit the maintenance plan after the wizard creates it. You should have objects for each step the maintenance plan is doing and you can go and edit each one.

Joie Andrew
"Since 1982"

arie01 Old Hand Points: 336 More actions · Answer 7

Hi Andrew,

Can you please provide an example?

Thank you,

arie01 Old Hand Points: 336 More actions · Answer 8

Hi guys,

Just to let you know that I think I figured it out. I recreated my maintenance plans and when I executed them manually, they worked exactly as I wanted.

Now I have to wait to see if they run on schedule as expected.

Thank you for all your help.

This thread can be archived now.

Arie