Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deleting backups on OS -- problem? Expand / Collapse
Author
Message
Posted Tuesday, May 21, 2013 5:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 21, 2014 6:12 AM
Points: 191, Visits: 634
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 // Junior DBA
11g OCA
10.5 newbie
Post #1454934
Posted Tuesday, May 21, 2013 6:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:46 AM
Points: 270, Visits: 487
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?
Post #1454976
Posted Tuesday, May 21, 2013 7:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 21, 2014 6:12 AM
Points: 191, Visits: 634
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 // Junior DBA
11g OCA
10.5 newbie
Post #1454990
Posted Tuesday, May 21, 2013 8:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:08 PM
Points: 167, Visits: 325
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
Post #1455049
Posted Tuesday, May 21, 2013 8:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 21, 2014 6:12 AM
Points: 191, Visits: 634
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 // Junior DBA
11g OCA
10.5 newbie
Post #1455055
Posted Tuesday, May 21, 2013 10:41 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 12, 2014 11:40 AM
Points: 2,795, Visits: 8,297
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.



Post #1455299
Posted Tuesday, May 21, 2013 11:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1455305
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse