SQL Server job to delete old backups

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » SQL Server job to delete old backups

12 posts, Page 1 of 2

1 2 »»

SQL Server job to delete old backups

Rate Topic

Display Mode

Topic Options

Author

Message

river1

Posted Monday, December 13, 2010 4:57 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

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

Adiga

Posted Monday, December 13, 2010 6:18 AM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:23 PM
Points: 1,618, Visits: 20,901

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

Joe Nakanishi-3

Posted Monday, December 13, 2010 12:18 PM

SSC-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

dwayne.bace

Posted Monday, December 13, 2010 1:31 PM

Grasshopper

Group: General Forum Members
Last Login: Sunday, February 06, 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

river1

Posted Tuesday, December 14, 2010 6:53 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

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

river1

Posted Tuesday, December 14, 2010 8:10 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

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

sqlbuddy123

Posted Tuesday, December 14, 2010 10:49 AM

Right there with Babe

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 3:17 PM
Points: 715, Visits: 1,524

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

dwayne.bace

Posted Tuesday, December 14, 2010 12:37 PM

Grasshopper

Group: General Forum Members
Last Login: Sunday, February 06, 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

river1

Posted Tuesday, December 14, 2010 2:19 PM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

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

Still same problem.

Any ideias?

Post #1034758

dwayne.bace

Posted Tuesday, December 14, 2010 2:56 PM

Grasshopper

Group: General Forum Members
Last Login: Sunday, February 06, 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 »

12 posts, Page 1 of 2

1 2 »»

Permissions