Deleting backups on OS -- problem?

  • 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

  • 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?

  • 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

  • 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

  • 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

  • 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.

  • 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