Deleting Old Backup files using T-SQL Script

Question

Deleting Old Backup files using T-SQL Script

karthik -450760

Ten Centuries

Points: 1362
More actions
March 24, 2010 at 2:56 am

#220310

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

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."

karthik -450760 Ten Centuries Points: 1362 More actions · Answer 2

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

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."

Jeffrey Williams SSC Guru Points: 90245 More actions · Answer 4

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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