Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 MS SQL Server 7 maintainence Plan Wizard

Author  Topic 

mAdMaLuDaWg
Starting Member

42 Posts

Posted - 2002-06-26 : 12:19:45
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.

chadmat
The Chadinator

1974 Posts

Posted - 2002-06-26 : 13:59:04
Appending data to the existing file won't help either. It will use the virtually the same amount of space, both backups will just be in one file.

Removing files older than X should work, try making it older than 23 hours or something like that. Maybe it hadn't actually been 24 full hours when the job ran.

-Chad

Go to Top of Page

solart
Posting Yak Master

148 Posts

Posted - 2002-06-26 : 14:09:54
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.

What you have done should work.

solart


Go to Top of Page

mAdMaLuDaWg
Starting Member

42 Posts

Posted - 2002-06-26 : 14:12:07
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.



Edited by - mAdMaLuDaWg on 06/26/2002 14:14:23
Go to Top of Page

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-06-26 : 14:30:12
That is correct. You will have to delete the files from the old maint plan manually. The 'delete files older than' option in Maintenance plans only applies to files it originally creates.

Jeff Banschbach
Consultant, MCDBA
Go to Top of Page

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-06-26 : 14:31:53
Have you tried using a backup/dump device?
It has an option to append.



Go to Top of Page

solart
Posting Yak Master

148 Posts

Posted - 2002-06-26 : 14:36:14
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.

Best Wishes

solart
Go to Top of Page

mAdMaLuDaWg
Starting Member

42 Posts

Posted - 2002-06-27 : 08:48:27
For some reason the backup that ran yesterday night didn't erase the older backups.. I wonder what could be possibly wrong over here. Is there anyway to get the plan to just overwrite the backups?

Go to Top of Page
   

- Advertisement -