April 27, 2006 at 3:31 pm
Hello,
I apologize if this has been covered, I couldn't find it if it was.
I have a system where I backup the databases each night to a file.
I also backup the transaction log every hour on the hour to another file.
I want to keep each days worth of log backups, until the next full backup occurrs.
So, let's say at 11:30PM I do a full DB Backup. In my log backup set, I have the 24 previous log backups (00:00 -> 23:00)
At Midnight when the next log backup runs, I want it to overwrite the previous days logs, as I now have a full backup to restore from and start over.
How do I do this?
I've tried creating a backup job, set the backupset to Expire after 1 day. on the options page I have append to existing Media and have checked the "check media set name and backupset expiration", but I don't think that actually purges any of the expired backups, the file just keeps on growing.
Thanks in advance,
John
April 27, 2006 at 7:57 pm
You'll have to set up a separate log backup that runs right after the full backup. This log backup must have the "Overwrite Existing Media" property checked if you are using the GUI (EM for SQL Server 2000 or SSMS for SQL Server 2005). If you are scripting the log backup, you'll use the "With INIT" option. The other 23 log backups will use the "WITH NOINIT" option.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
April 27, 2006 at 8:57 pm
That's a good idea. Thanks for the help.
John
April 28, 2006 at 5:55 am
Just to share --
In SQL 2005 Management Studio under Management, Maintenance Plans, right click: I have also create a "New Maintenance Plan"- where there is a Cleanup Backup Task that I can use to create a Cleanup Backup plan to purge out my old backup files.
In the "New Maintenance Plan" select the Maintenance Cleanup Task (on the left side). Then in the Design tab that comes up, double-click the task to configure it, browse to the folder holding backups, specify backup extension (so here you could specify to cleanup your trn backups), and set the backup retention (1 day for you) in the File Age section. Click ok and Save Selected item under the File menu.
Also rather than creating a seperate Cleanup Plan you can also add this to your existing full backup maintenance plan by adding this "Maintenance Cleanup Task" directly - so after executing the full backup portion of the plan it would then cleanup the TRN backup files.
Since as I described above you have to browse to the folder where your backups are located- if you have multiple backup directories this option can get more costly since you would need seperate cleanup steps or plans..
As I said- just thought I would mention another option I've used.. I agree that the option above sounds pretty straightforward though.. good luck.
July 26, 2006 at 4:44 pm
I have not been able to successfully delete backups with a Maintenance Cleanup Task that are pointed to a remote backup server. It only appears to let you perform the backup maintenance on local directories. Has anyone been able to successfully use the Maintenance Cleanup Task to maintain file retention on a remote server. There was no problem doing this in SQL2000. Any help would be appreciated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply