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 12»»

SQL Server job to delete old backups Expand / Collapse
Author
Message
Posted Monday, December 13, 2010 4:57 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:08 AM
Points: 744, Visits: 1,056
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
Post #1033707
Posted Monday, December 13, 2010 6:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
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
Post #1033746
Posted Monday, December 13, 2010 12:18 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:38 AM
Points: 170, 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).
Post #1034018
Posted Monday, December 13, 2010 1:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, February 6, 2011 2:36 PM
Points: 16, 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
Post #1034057
Posted Tuesday, December 14, 2010 6:53 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:08 AM
Points: 744, Visits: 1,056
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
Post #1034347
Posted Tuesday, December 14, 2010 8:10 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:08 AM
Points: 744, Visits: 1,056
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?

Post #1034450
Posted Tuesday, December 14, 2010 10:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 11, 2014 2:28 PM
Points: 1,194, Visits: 2,219
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



Post #1034586
Posted Tuesday, December 14, 2010 12:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, February 6, 2011 2:36 PM
Points: 16, 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
Post #1034659
Posted Tuesday, December 14, 2010 2:19 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:08 AM
Points: 744, Visits: 1,056
I have already told the job that (on sucess go to and on failure go to...)

Still same problem.

Any ideias?
Post #1034758
Posted Tuesday, December 14, 2010 2:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, February 6, 2011 2:36 PM
Points: 16, 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
Post #1034797
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse