Hello, I'm using Microsoft SQL 7 Enterprise Manager to back up my databases. In Enterprise Manger, I have Schedule a daily Maintainence plan to back up all the the databases. The problem is that every day the backup is put into a different file thus using up HD space. Is there any way to append data to the current backup? I tried selecting the option 'Remove Files older than 1 day' but its not working. Any help would be greatly appreciated. Thanks.
Make sure SQL Agent is running. This should take the backup and delete the files older than 1 day. When you changed the parameter to delete files older than 1 day, was the path and file name specified the same (identical) to when the maintenance plan was setup before specifying delete files older than one day?
USE msdb EXEC sp_delete_backuphistory '08/20/98'
Books On Line (BOL) says the above will help clean-up the MSDB database. You could use it to clean-up the MSDB database and use the specified date to manually delete the backup files.
Thanks. But I turned it on yesterday morning and it ran in the night. There are 7 day old backups in the directory and none of them got deleted. Perhaps due to the fact that the other 7 backups were performed by a different maintainence plan with the same dbs? Now the plan produces a different name for the file each time it runs... it appends the date to the end of the file name of each back up of each database.
As you noticed, the date and time of a backup is appended to the backup file created by the maintenance plan. I think CHADMAT is correct in that when the maintenance plan executes, it takes both date and time into consideration in determining if a file has aged to the point where it can be deleted. In other words, saying to keep a file for one day is really saying to keep it for 24 hours from its creation date/time.
Sorry I can't offer anymore knowledge about this topic.