SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server job to delete old backups


SQL Server job to delete old backups

Author
Message
river1
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4542 Visits: 1366
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
Adiga
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6488 Visits: 21012
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
Joe Nakanishi-3
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 109
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
dwayne.bace
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 84
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
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4542 Visits: 1366
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
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4542 Visits: 1366
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
sqlbuddy123
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6390 Visits: 2243
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
dwayne.bace
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 84
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
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4542 Visits: 1366
I have already told the job that (on sucess go to and on failure go to...)

Still same problem.

Any ideias?
dwayne.bace
dwayne.bace
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 84
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
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