May 21, 2013 at 5:30 am
Hi guys,
Here we have a 2005 XE instance (XE has no maintenance plans). There is a script that backs up the databases everyday keeping 7 copies (_1 to _7 with INIT to overwrite). However, 7 days is too much for the FS so I've created a .bat script removing the .bak files older than 3 days.
My question is, is it OK to remove the files this way? Or will it break SQL Server during the recovery process (since it can't find the old backup files)? Every day is a full backup so it only really needs the previous days backup (at least at the organisation level; not sure if SQL demands them since it will have backup history with them).
Edit: My guess is that it wouldn't be an issue besides the fact that msdb would continue to grow slightly (since the backup list isn't maintained); is this right?
Thanks for any info
Dird
May 21, 2013 at 6:58 am
I'm not talking from first hand experience, but it looks like it should be fine:
http://msdn.microsoft.com/en-GB/library/ms191304(v=sql.90).aspx
Optionally, specify:
- The INIT clause to overwrite the backup media, and write the backup as the first file on the backup media. If no existing media header exists, one is automatically written.
Also, when you first start this sequence of backups going, there would not be anything to overwrite, so surely it would not break just because it doesn't have anything to overwrite now?
May 21, 2013 at 7:17 am
Ross.M (5/21/2013)
Also, when you first start this sequence of backups going, there would not be anything to overwrite, so surely it would not break just because it doesn't have anything to overwrite now?
My breakage question wasnt about this but about deleting old backups from the OS without removing them from the msdb tables.
Dird
May 21, 2013 at 8:37 am
Deleting file from filesystem using a BATCH file won't have any issues in your restore process / MSDB.
The only thing in my view you should be concerned about is ensuring that you always have latest backup in filesystem first and then call the batch file accordingly to delete the oldest backup. If for some reason latest backup has failed your batch file should not delete the oldest backup.
Ankit
May 21, 2013 at 8:43 am
Ankit Mathur-481681 (5/21/2013)
The only thing in my view you should be concerned about is ensuring that you always have latest backup in filesystem first and then call the batch file accordingly to delete the oldest backup. If for some reason latest backup has failed your batch file should not delete the oldest backup
Yes I thought of this issue (backups failing for 5 days) and added a part in the batch file to prevent deletions in this case. Thanks.
Dird
May 21, 2013 at 10:41 pm
Dird (5/21/2013)
Ross.M (5/21/2013)
Also, when you first start this sequence of backups going, there would not be anything to overwrite, so surely it would not break just because it doesn't have anything to overwrite now?My breakage question wasnt about this but about deleting old backups from the OS without removing them from the msdb tables.
Dird
No problem.
May 21, 2013 at 11:03 pm
Dird (5/21/2013)
Edit: My guess is that it wouldn't be an issue besides the fact that msdb would continue to grow slightly (since the backup list isn't maintained); is this right?
I schedule a job on all my instances to delete old backupsets so msdb does not have this type of buildup of old data:
DECLARE @dt DATETIME ;
SET @dt = DATEADD(day, -60, GETDATE())
EXEC msdb.dbo.sp_delete_backuphistory
@oldest_date = @dt ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply