SQL Server job to delete old backups

  • river1

    SSC-Insane

    Points: 24126

    Hi,

    I need to create a job that cleans backups and log backups that are older than a month.

    I do not want to create a maintenance plan, what i would like to create is a single job.

    Is it possible?

    How to do something like this?

    Tks,

    Pedro

  • Adiga

    One Orange Chip

    Points: 27224

    The Cleanup task in Maintenance Plans is the easiest way of doing this. Since you do not want to create Maintenance Plans, you can make use of a custom as explained here

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • Joe Nakanishi-3

    Right there with Babe

    Points: 798

    You can use xp_delete_file (http://sqlblog.com/blogs/andy_leonard/archive/2009/03/11/xp-delete-file.aspx) or you could write something using xp_cmdshell (assuming you have xp_cmdshell enabled).

  • dwayne.bace

    Mr or Mrs. 500

    Points: 592

    I use the following stored proc to delete old backup files as a step within a standard scheduled job.

    usage: usp_DeleteOldBackupFiles <path>, <file extention>, <age_hours>

    i.e. usp_DeleteOldBackupFiles 'D:\MSSQL_DBBackups', 'bak', 36

    usp_DeleteOldBackupFiles 'D:\MSSQL_DBBackupsLogs', 'trn', 72

    --====================================================================

    CREATE PROCEDURE [dbo].[usp_DeleteOldBackupFiles]

    @path nvarchar(256),

    @extention nvarchar(10),

    @age_hrs int

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @DeleteDate nvarchar(50)

    DECLARE @DeleteDateTime datetime

    SET @DeleteDateTime = DateAdd(hh, -@age_hrs, GetDate())

    SET @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

    EXECUTE master.dbo.xp_delete_file 0, @path, @extention, @DeleteDate, 1

    END

    --============================

    -- xp_delete_file information

    --============================

    -- xp_delete_file actually checks the file header to see what type of file it is and will only delete certain types such

    -- as database and log backups. I suppose they expanded this to certain types of log files as well but as you say this is

    -- not documented by MS. Just be aware that it will not delete just any file type

    -- First argument is:

    -- 0 - specifies a backup file

    -- 1 - specifies a report file

    -- (I'm not sure what the difference between a "backup file" and a "report file" is, since you specify the extension of files

    -- you're deleting with the third argument.)

    --

    -- Fifth argument is whether to delete recursively.

    -- 0 - don't delete recursively (default)

    -- 1 - delete files in sub directories

    --====================================================================

    I tweeked this from information I found on the net - Unfortunately, I can't remember where from so can't offer credit where it's due 🙁

    Cheers

  • river1

    SSC-Insane

    Points: 24126

    Thank you for your reply.

    I have two question about your stored procedure:

    1) do i have to activate in the Surface area configuration the xp_cmdshell to use your procedure?

    2) Where do you stored this stored procedure? In the MSDB or in the Master Database?

    Thank you

  • river1

    SSC-Insane

    Points: 24126

    I am thinking in using your stored procedure:

    use msdb

    go

    CREATE PROCEDURE [dbo].[usp_DeleteOldBackupFiles]

    @path nvarchar(256),

    @extention nvarchar(10),

    @age_hrs int

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @DeleteDate nvarchar(50)

    DECLARE @DeleteDateTime datetime

    SET @DeleteDateTime = DateAdd(hh, -@age_hrs, GetDate())

    SET @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

    EXECUTE master.dbo.xp_delete_file 0, @path, @extention, @DeleteDate, 1

    END

    /* I want to create this procedure in the MSDB database.*/

    Then, in each job (i have two jobs, one to do database backups and another to do log backups) i add a new step, it will be the first step of each job. The code is this:

    For database backups:

    usp_DeleteOldBackupFiles 'D:\MSSQL_DBBackups', 'bak', 720

    For log backups:

    usp_DeleteOldBackupFiles 'D:\MSSQL_DBBackupsLogs', 'trn', 720

    /* 720 hours = aprox. 1 month, 30 days*/

    As i told, this will be the first step of each job.

    When i try to save this first step, i receive the following message from SQL Server:

    Warning: The following job steps cannot be reached with the current job step flow logic:

    [1] DeleteBackups

    Is this the intended behavior?

    Can you help with this? why SQL Server sends a message like this?

  • SQLBuddy123

    One Orange Chip

    Points: 28294

    This happens if your job has multiple steps say Step1 and Step2.

    For each step, you need to specify the SQL Server agent what to do in case of success or failure of that step.

    In this case , suppose that

    Step 1 : Delete Old Backups

    Step 2 : Take Backup

    Then for Step1 and Step2 you need to specify some thing like this

    Step1 : On Success ---> Goto Step 2

    On Failure ---> Quit the job reporting failure or Goto Step 2

    Step2 : On Success ---> Quit the job reporting success.

    On Failure ---> Quit the job reporting failure.

    Thank You,

    Best Regards,

    SQLBuddy

  • dwayne.bace

    Mr or Mrs. 500

    Points: 592

    Hi OldHand

    1) do i have to activate in the Surface area configuration the xp_cmdshell to use your procedure?

    No. Thats not required.

    2) Where do you stored this stored procedure? In the MSDB or in the Master Database?

    I always create a DBA/Management database on my SQL servers to hold these type of stored procs/functions/tables/logging/performance info/service broker queues...etc. Personally, I try to avoid adding customisations to any of the system databases, although I'm aware this is commonly done.

    Cheers

  • river1

    SSC-Insane

    Points: 24126

    I have already told the job that (on sucess go to and on failure go to...)

    Still same problem.

    Any ideias?

  • dwayne.bace

    Mr or Mrs. 500

    Points: 592

    Hi river1

    The issue you are seeing is definately related to the flow control you have set up in the job.

    I suggest you open the job, and check the steps very carefully. Look at the order that the steps are listed in the job. They may not be listed in the order you create them....i.e. If you create a step (Delete Old Backups), then simply click 'new' to create a second job (Do Backups), the order will be: Step 1. Do Backups, Step 2.Delete Old Backups.

    You can change the order with the 'move step' buttons,

    If you still have problems, script the job and post it here

    Cheers

  • SanjayAttray

    SSChampion

    Points: 13157

    @river1

    You can use this script. work well with 2005 and 2008 versions and I am using this for last 4 years and never had issue.

    -- Delete backup files from any folder. just change path. Add this TSQL code as step2 to scheduled plan

    DECLARE @currentdate datetime

    DECLARE @olddate datetime

    set @currentdate = CURRENT_TIMESTAMP

    set @olddate = @currentdate - 2 -- Here change the number as required for how many days backup you want to keep.

    EXECUTE master.dbo.xp_delete_file 0,N'D:\Backup directory path here',N'bak',@olddate,1

    SQL DBA.

  • SQLactions

    Old Hand

    Points: 324

    When i try to save this first step, i receive the following message from SQL Server:

    Warning: The following job steps cannot be reached with the current job step flow logic:

    [1] DeleteBackups

    Is this the intended behavior?

    Can you help with this? why SQL Server sends a message like this?

    Hi,

    Check this blog for the cause and resolution of this error.

    Prashant
    Check out my blog at http://sqlactions.com

  • manojdemel

    Valued Member

    Points: 59

    Hi Guys,

    You can use SQL Maintenance task (Management > Maintenance Plans > New > Cleanup Task) to delete any files based on the age of it.

    If you want to delete files by calling a stored procedure, you can use the system stored procedure:

    EXECUTE master.dbo.xp_delete_file 0,N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup',N'bak',N'2016-07-17T23:02:07'

  • wasim.ahmad780

    SSC Enthusiast

    Points: 177

    dwayne.bace - Tuesday, December 14, 2010 2:56 PM

    Hi river1The issue you are seeing is definately related to the flow control you have set up in the job.I suggest you open the job, and check the steps very carefully. Look at the order that the steps are listed in the job. They may not be listed in the order you create them....i.e. If you create a step (Delete Old Backups), then simply click 'new' to create a second job (Do Backups), the order will be: Step 1. Do Backups, Step 2.Delete Old Backups.You can change the order with the 'move step' buttons, If you still have problems, script the job and post it hereCheers

    Hi how to add check condition 
    unless db backup success then delete old backup if db backup failure of any reason then don't delete old backup

  • JakDBA

    SSCommitted

    Points: 1610

    You can try using the power shell script... we are using the same.

    https://gallery.technet.microsoft.com/scriptcenter/Delete-files-older-than-x-13b29c09

    @JayMunnangi

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply