SQL Server Performance Forum – Threads Archive

How to delete backup files older than 2 days in SQ

There used to be an option in SQL server 2000 while creating maintenance plan that allows us to delete files older than 2 days or any particular number of days, but while creating plans in SQL server 2005 , I dont find that option anywhere, how do I delete the files before my disk gets full? I would appreciate any help in this area Thanks
Satya

You can find that feature in ‘Maintenance Cleanup Task’ GUI ——————
Bug explorer/finder/seeker/locator
——————

It cleans out information from Datebase logs but does not delete the files completely, is there any solution for completely deleting the files?

check carefully ‘Cleanup history’ and ‘Maintenance Cleanup Task’ are different. ——————
Bug explorer/finder/seeker/locator
——————

Ok Thanks I will try that out

Still it is not working…
You can schedule the command using extended proc.
declare @dt datetime
— delete older than two days…
select @dt = getdate()-2
EXECUTE master.dbo.xp_delete_file 0,N’D:BackupFolder’,N’BAK’,@dt Mohammed U.

Ya I too have tried using the maintenance cleanup task, but tht does’nt work out. Mohammed I will try what u have suggested. Thanks
Satya

I am sorry but that the Maintenance cleanup task worked out, the files are getting deleted, but the problem is I have all the backup files in subfolders in a given foldeer, I know there is an option of include the sub-folders during setting up the task, but I am not able to get that option anywhr while creating a plan with Maintenance cleanup task Thanks
Satya

Hi, Mohammed, is there some extended SPs within sQL Server for copying files with the similar function as windows cmd Xcopy. Thanks in advance.
——————
Bug explorer/finder/seeker/locator
——————

Please note about using undocumented XPs in this case, as they may be deleted or changed in next release of hotfix or service pack, so use them with utmost care. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

satya.sqldba – One more Satya [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Yes, confusing.<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> —————— Bug explorer/finder/seeker/locator ——————

I believe not with the user name & signature [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]. <hr noshade size="1">Satya SKJ Microsoft SQL Server MVP Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing.</center>

I don’t think there is any procedure to copy the files but you can make use of XP-cmdshell… Mohammed U.

There are few improvements in SP2 for MPs…
http://download.microsoft.com/downl…423D-BC8F-B11ECD4195B4/WhatsNewSQL2005SP2.htm Maintenance Plans
Multiple schedules. The Maintenance Plan Designer supports multiple subplans for a maintenance plan. Each of the subplans can have independent schedules for their maintenance tasks.
Multiple server administration. Maintenance plans will work on multiple servers using master server (MSX), target server (TSX).
The Maintenance Plan Wizard now includes the highly sought after Maintenance Cleanup task that was in SQL Server 2000. This task removes files remaining from executing a maintenance plan.
Multiple fixes to individual tasks:
The Database Backup maintenance plan task now includes the ability to specify the backup expiration option that was previously available in SQL Server 2000.
The Database Backup maintenance plan task no longer changes the folder location for the backup if it is set to something other than the default location.
The Backup Database maintenance plan task prohibits the ability to mistakenly set the option to create differential and transaction log backups for system databases.
The History Cleanup maintenance plan task includes the ability to set the option to delete files using a unit in hours.
The Update Statistics task includes options for full scan or for sample size as was available in SQL Server 2000 maintenance plans. Mohammed U.

The option to remove files in first-level subfolders is added in SP1.

]]>