Deleting Old Backup files using T-SQL Script

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Deleting Old Backup files using T-SQL Script

Deleting Old Backup files using T-SQL Script

Rate Topic

Display Mode

Topic Options

Author

Message

karthik -450760

Posted Wednesday, March 24, 2010 2:56 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 31, 2010 1:05 PM
Points: 44, Visits: 245

Hi,

Is there a way to delete old backup files without using Xp_cmdshell and scheduling maintenance plans? I tried using xp_delete_file but it works only for native SQL backups and not for litespeed backups.

Any help is appreciated..

Thanks!!!

Post #888752

free_mascot

Posted Wednesday, March 24, 2010 4:20 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 3:39 AM
Points: 1,768, Visits: 1,312

Yes, it is quite possible if you are using sql 2005 or higher.

Create a Maintenance plan and select maintenance cleanup Task. Go to property of the Task and there you will have option to delete specific file extension.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Post #888796

karthik -450760

Posted Wednesday, March 24, 2010 4:30 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 31, 2010 1:05 PM
Points: 44, Visits: 245

Thanks for the reply but is it possible to delete backups without maintenance plans? I am trying to write a script which takes backup of each database and immediately delete it.. The script should delete the old backup of the database immediately after the new backup is taken... For eg:

declare cursor bkup select name from sysdatabases
open bkup

......
...

......

xp_backup_database @dbname .................

delete old backup files

.........

fetch next from bkup
......

Thanks for your help!!!!

Post #888802

free_mascot

Posted Wednesday, March 24, 2010 6:46 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 3:39 AM
Points: 1,768, Visits: 1,312

You have to create maintenance plan. But it is possible that you do not include any backup step. Also you can have only deleting files Task to your maintenance.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Post #888895

Jeffrey Williams 3188

Posted Wednesday, March 24, 2010 1:00 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:21 AM
Points: 4,317, Visits: 9,216

Litespeed comes with their own xp procedure to delete litespeed based backup files. Look in the documentation for Litespeed and it will show you how to script it out yourself.

The procedure you'll want to use is: xp_slssqlmaint

The parameters will be in a single string and will be something like:

' -MAINTDEL -DELFOLDER "backup folder" -DELEXTENSION "backup extension here"'
-DELUNIT "unit number - eg 60" -DELUNITTYPE "minutes" -DELUSEAGE';

You can delete files based upon days, hours, minutes, etc...

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Post #889276

DBA in Unit 7

Posted Wednesday, March 24, 2010 1:17 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 12:34 PM
Points: 195, Visits: 1,057

Post #889285

karthik -450760

Posted Thursday, March 25, 2010 12:14 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 31, 2010 1:05 PM
Points: 44, Visits: 245

thanks a lot Jeff!!!! I will try and let you know

Post #889551

Nick B.

Posted Monday, April 05, 2010 7:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 11:48 AM
Points: 6, Visits: 111

The suggestions about maintenance plans actually helps me out, good topic, much thanks.

Post #896674

« Prev Topic | Next Topic »

Permissions