Deleting Older backups

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » Deleting Older backups

Deleting Older backups

Rate Topic

Display Mode

Topic Options

Author

Message

sql_time

Posted Thursday, April 01, 2010 12:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, August 16, 2010 4:03 PM
Points: 7, Visits: 35

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

Post #894446

SQLRNNR

Posted Thursday, April 01, 2010 12:44 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:46 PM
Points: 18,732, Visits: 12,329

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 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #894461

m--S3qU3L

Posted Saturday, April 03, 2010 3:08 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, November 19, 2012 11:30 PM
Points: 290, Visits: 713

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

Post #896321

Steve Jones - SSC Editor

Posted Saturday, April 03, 2010 4:09 PM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406, Visits: 13,722

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/

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #896324

m--S3qU3L

Posted Saturday, April 03, 2010 4:29 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, November 19, 2012 11:30 PM
Points: 290, Visits: 713

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

Post #896330

Paul White

Posted Sunday, April 04, 2010 6:59 AM

SSChampion

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989, Visits: 10,529

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
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #896403

Jeffrey Williams 3188

Posted Sunday, April 04, 2010 9:10 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 4,317, Visits: 9,216

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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

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

Post #896420

Paul White

Posted Sunday, April 04, 2010 9:26 AM

SSChampion

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989, Visits: 10,529

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
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #896421

sql_time

Posted Sunday, April 04, 2010 3:23 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, August 16, 2010 4:03 PM
Points: 7, Visits: 35

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.

Post #896488

« Prev Topic | Next Topic »

Permissions