SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 MS SQL Server 7 maintainence Plan Wizard
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mAdMaLuDaWg
Starting Member

42 Posts

Posted - 06/26/2002 :  12:19:45  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 06/26/2002 :  13:59:04  Show Profile  Visit chadmat's Homepage  Reply with Quote
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

USA
148 Posts

Posted - 06/26/2002 :  14:09:54  Show Profile  Reply with Quote
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 - 06/26/2002 :  14:12:07  Show Profile  Reply with Quote
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

USA
478 Posts

Posted - 06/26/2002 :  14:30:12  Show Profile  Visit efelito's Homepage  Reply with Quote
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

United Kingdom
616 Posts

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



Go to Top of Page

solart
Posting Yak Master

USA
148 Posts

Posted - 06/26/2002 :  14:36:14  Show Profile  Reply with Quote
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 - 06/27/2002 :  08:48:27  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000