Job to clean old backups

Last Post 05 Aug 2012 01:33 AM by Prashant Kumar. 7 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
river1
New Member
New Member

--
13 Dec 2010 02:56 AM
Hi Masters,

   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
rm
New Member
New Member

--
13 Dec 2010 04:56 AM
You can create that single job with maintenance plan, or just script out maintenance plan's 'maintenance clean' task and run it as sql job.
gunneyk
New Member
New Member

--
13 Dec 2010 07:58 AM
The maintenance plan uses xp_filedelete and as long as the backups are native SQL Serveror compressed SQL Server ones it should work fine. Create a maint plan delete backup job and you can see the syntax it uses then create your own.
river1
New Member
New Member

--
14 Dec 2010 06:08 AM
I am thinking in using this 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 master help with this? why SQL Server sends a message like this?















rm
New Member
New Member

--
14 Dec 2010 09:31 AM
You have to set the job to start with first step.
river1
New Member
New Member

--
14 Dec 2010 12:22 PM
Where can i say that Master rm?
gunneyk
New Member
New Member

--
14 Dec 2010 04:20 PM
You set the order in the Advanced tab of each Job Step. If the first one says On Success Action : Quit the job reporting Success then it will never reach the 2nd step. You need to change it to Go To Next Step.
Prashant Kumar
New Member
New Member

--
05 Aug 2012 01:33 AM
Hi,

Check out this blog for troubleshooting the flow logic error.


< LINK REMOVED >
You are not authorized to post a reply.

Acceptable Use Policy