Deleting Older backups

Question

Deleting Older backups

sql_time

SSC-Addicted

Points: 479
More actions
April 1, 2010 at 12:04 am

#217362

Hi guys, I am new to sql and Litespeed
Here is my situation
We take daily Production(PRD) backup which is litespeed backup using sql serevr 2005 and transfer or FTP to other servers (UAT) for storing for 3 days or less.I want to create a maintenance plan using SQL or Litespeed to delete older backups from the particular folder.
I crated Maintenance plan using SQL 2005 in UAT server to delete 3 days old PRD backups. The job executes with out any error and shows it successful but its not deleting any backups.I tried all types of file extensions like bak, .bak, *.*
Do you think UAT sql 2005 is not able to delete backups created by PRD sql server 2005?
Do you think sql 2005 is not capable of deleting litespeed backups?
Is there any other way to create a maintenance plan using litespeed to delete old backups please suggest.
Thanks

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

I think I would explore other alternatives than the maint plan from the UAT server. Some possible options: ssis package, Powershell, sql script using xp_cmdshell, or an OS script that runs to perform this task.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

m--S3qU3L Hall of Fame Points: 3706 More actions · Answer 2

Create an SP which uses the xp_cmdshell and use the del command along with it.

Call the SP through a SQL agent job. Schedule the job for daily run.

John

Steve Jones - SSC Editor SSC Guru Points: 734416 More actions · Answer 3

I would think that it could delete the backups, but perhaps it's looking in msdb for something.

What I'd suggest is that you look for a script with Powershell or VBScript/FileSystemObject that deletes files older than xx days. Then run that from Agent as a Command Shell command.

Here's one: http://www.sqlservercentral.com/scripts/Miscellaneous/31627/

m--S3qU3L Hall of Fame Points: 3706 More actions · Answer 4

Hi,

I believe, we can also take the date info with the help of MS DOS commands through xp_cmdshell and take it for the calculation of backup file retention and deletion.

So, we may not have to go to msdb.dbo.backupset for the date info.

Steve, correct me if I'm wrong.

John

Paul White SSC Guru Points: 150467 More actions · Answer 5

sql_time (4/1/2010)
Do you think sql 2005 is not capable of deleting litespeed backups?

I believe that the Maintenance Clean-up Task can only clean up backup files created directly by the Back Up Database Task. As far as I remember, LiteSpeed backups can only be invoked by calling an extended stored procedure, and so have to be set up using the Execute T-SQL Statement Task.

Unless you are able to use native SQL Server backup rather than LiteSpeed (allowing you to use the Back Up Database Task), it looks like you will have to write a script manually to find and delete old files. Be very careful with this, as it is all too easy for a bug in the script to delete files it should not.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 6

Litespeed comes with it's own litespeed maintenance utility for deleting old backup files. Look in the documentation for litespeed on the command line utilities.

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

Paul White SSC Guru Points: 150467 More actions · Answer 7

Just checked the Quest site, and it appears that the latest release (5.2.0) does support the Cleanup History task. See http://www.quest.com/Quest_Site_Assets/PDF/LiteSpeed_520_ReleaseNotes.htm

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

sql_time SSC-Addicted Points: 479 More actions · Answer 8

Hello Guys,

Thanks for every one for your valuable suggestions, as suggested by Jeffrey Williams I created a maintenance plan using clean up maintenance task in Litespeed, and it works perfectly right.

The lite speed architecture is almost similar to SQL maintenance task, its easy as sql.I created the task one week back and its working fine.

Once again Thanks for every one, u guys rock.