When adding Maintenance Cleanup Task to your maintenance plan...

Question

When adding Maintenance Cleanup Task to your maintenance plan...

jason.stephens

Ten Centuries

Points: 1088
More actions
July 28, 2008 at 11:28 am

#188803

When adding Maintenance Cleanup Task to your maintenance plan it appears you can not select 1 day and it clean up yesterdays. Do I have to select 23 hours or am I doing it incorrectly? If I do a backup every night at 10 and all I have is the backup and the cleanup in the plan, it is keeping both yesterdays and todays. How do I fix this?

Viewing 7 posts - 1 through 6 (of 6 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

if you only want one day then yes make retemtion period 23 hours or less depending on how long backup takes. time recorded on .bak file is when it completes, so if it takes any time will be less than 1 day old.

preferable solution would be don't use mainteennace plan for backup, just have a sqlagent job , write to same file each night and use the with init clause in the backup

---------------------------------------------------------------------

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

I would not recommend one file that you write to every night. If there is a problem with the backup file, you stand to lose two days worth of data, not one.

I always write to new files, trimming off the old ones with the maintenance cleanup task as needed. I also copy them to a remote server to have a second copy.

jason.stephens Ten Centuries Points: 1088 More actions · Answer 3

Only need one night because we have a backup system (Tivoli) that backs up everything on the server every night. In the backup plans for Tivoli, we save something like 21 days of database backups before it starts to purge them. So, only the latest one is necessary on the server to be backuped up when Tivoli kicks off at 2 AM.

Thanks for the replies gentlemen!

Irish Hall of Fame Points: 3958 More actions · Answer 4

Jason,

I'm a nervous nelly when it comes to database backups. You are right, only the last file is really necessary to backup to Tivoli, but what happens if the file is corrupt (event if it is initialized nightly) when it gets written to tape?

I would keep 2 days of backups on disk, with different file names (append date time), and still keep backing them up to Tivoli. I would then use a maintenance job or T-SQL to clear out backup files that are older than 2 days.

In my humble opinion, you are better covered with more backups and redundancy than less.

Regards, Irish

george sibbald SSC Guru Points: 104210 More actions · Answer 5

got to agree it is worth keeping at least two backups on disk, especially as if you have to recover a database its always possible it is required to recover to at some point the previous day (which means two days worth of tran log backups as well). You can also recover the database faster if the backup is already present on the server.

However I was presuming Jason did not have the space for more than one backup on disk which makes the backup with init to the same file the best option IMHO.

This was especially true with SQL2000 maintenance plans as the cleanup happened after the backup, so even if you only wanted one backup, you needed enough space for two. With 2005 I guess you can run the cleanup step before the backup step, but then you risk having no backups!.

---------------------------------------------------------------------

jason.stephens Ten Centuries Points: 1088 More actions · Answer 6

george sibbald (7/29/2008)
got to agree it is worth keeping at least two backups on disk, especially as if you have to recover a database its always possible it is required to recover to at some point the previous day (which means two days worth of tran log backups as well). You can also recover the database faster if the backup is already present on the server.
However I was presuming Jason did not have the space for more than one backup on disk which makes the backup with init to the same file the best option IMHO.
This was especially true with SQL2000 maintenance plans as the cleanup happened after the backup, so even if you only wanted one backup, you needed enough space for two. With 2005 I guess you can run the cleanup step before the backup step, but then you risk having no backups!.

You are correct about the space. Some of our databases are very large and with so many systems, even the medium sized databases add up quickly. Thank you all for taking the time out of your day to bring your knowledge to the table for me. I can read books all day long, but I seem to get so much more from talking with others that do work with it.