Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deleting backups on OS -- problem?


Deleting backups on OS -- problem?

Author
Message
Dird
Dird
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 771
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


Dird
RossRoss
RossRoss
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 762
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?
Dird
Dird
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 771
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


Dird
Ankit Mathur-481681
Ankit Mathur-481681
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 328
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
Dird
Dird
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 771
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


Dird
homebrew01
homebrew01
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2986 Visits: 9071
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.



Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8259 Visits: 14368
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search