Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deleting Old Backup files using T-SQL Script


Deleting Old Backup files using T-SQL Script

Author
Message
karthik -450760
karthik -450760
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
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!!!
free_mascot
free_mascot
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2879 Visits: 2235
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
karthik -450760
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
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!!!!
free_mascot
free_mascot
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2879 Visits: 2235
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 3188
Jeffrey Williams 3188
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: 4463 Visits: 9833
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 opportunities brilliantly disguised as insurmountable obstacles.

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

DBA in Unit 7
DBA in Unit 7
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 1124
.
karthik -450760
karthik -450760
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 245
thanks a lot Jeff!!!! I will try and let you know
Nick B.
Nick B.
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 111
The suggestions about maintenance plans actually helps me out, good topic, much thanks.
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