SQLServerCentral / Administering / SQL Server 2005 / Delete old Backup files using T-sql script / Latest Posts

RE: Delete old Backup files using T-sql script

MudLuck — Thu, 06 Dec 2012 11:32:22 GMT

I hope you don't mind but I posted this about you on facebook :)"I like this guys code very robust and feature complete....."

RE: Delete old Backup files using T-sql script

Musab-946967 — Mon, 02 Jul 2012 23:48:05 GMT

use "forfiles" with xp_cmdshell and it will serve your purpose forfiles [/p <Path>] [/m <SearchMask>] [/s] [/c "<Command>"] [/d [{+|-}][{<Date>|<Days>}]]http://technet.microsoft.com/en-us/library/cc753551(v=ws.10)

RE: Delete old Backup files using T-sql script

SQLRNNR — Wed, 07 Mar 2012 12:36:45 GMT

Here's an alternative[url]http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/87652/[/url]

RE: Delete old Backup files using T-sql script

logicinside22 — Wed, 07 Mar 2012 11:12:11 GMT

i am trying your SP and it's not deleting the old file..can you explain in detail if have to setup differently?Thanks

RE: Delete old Backup files using T-sql script

logicinside22 — Tue, 17 Jan 2012 13:01:27 GMT

Thanks Jasonany idea about to specify path for t-log backup file. my t-log backup file is on network drive.

RE: Delete old Backup files using T-sql script

SQLRNNR — Tue, 17 Jan 2012 12:51:48 GMT

Yes change the 5 to a 7

RE: Delete old Backup files using T-sql script

logicinside22 — Tue, 17 Jan 2012 12:47:00 GMT

It seems to be a powerful script . i tried on local machine and it has deleted all backup files.so u mean to say if i want to delete 7 day older backup t-log backup files only i have to put 7 instead of 5?I just want to delete T-log backup file so how i can set in script?WHERE a.backup_start_date < GETDATE()-5

RE: Delete old Backup files using T-sql script

SQLRNNR — Tue, 17 Jan 2012 12:23:06 GMT

[quote][b]logicinside22 (1/17/2012)[/b][hr]@Manjunath Hi i am working on same script. can you let me know how i can provide the network share location to delete old t-log backup file in your script?Also i want to delete file 7 days older than so how i can set that in your script??otherwise its working fine .thanks for nice posting[/quote]This code segment is what you will modify for the date[code="sql"]WHERE a.backup_start_date < GETDATE()-5[/code]It pulls the logfile location from the database.

RE: Delete old Backup files using T-sql script

logicinside22 — Tue, 17 Jan 2012 12:07:47 GMT

@Manjunath Hi i am working on same script. can you let me know how i can provide the network share location to delete old t-log backup file in your script?Also i want to delete file 7 days older than so how i can set that in your script??otherwise its working fine .thanks for nice posting

RE: Delete old Backup files using T-sql script

bhat.manjunath7 — Tue, 06 Jul 2010 12:14:57 GMT

Hi,Here is a small piece of code which suits ur requirement. Kindly Go through and let me know if you need any thing else.SET NOCOUNT ONGOsp_configure 'show advanced options', 1GORECONFIGUREGOsp_configure 'xp_cmdshell', 1GOPRINT 'Running reconfigure'RECONFIGUREGODECLARE @rc INTDECLARE @cmd VARCHAR(1000)DECLARE @cursor CURSORDECLARE @physicaldevicename VARCHAR(1000)DECLARE @Err VARCHAR(1000)SET @cursor = CURSOR FOR SELECT b.physical_device_name AS physicaldevicename FROM msdb..backupset AS a inner join msdb..backupmediafamily as b on a.media_set_id = b.media_set_id WHERE a.backup_start_date < GETDATE()-5 ORDER BY a.backup_start_date,a.database_nameOPEN @cursorWHILE 1=1BEGIN FETCH FROM @cursor INTO @physicaldevicename IF @@fetch_status <> 0 BEGIN SET @Err = @@fetch_status PRINT @Err BREAK END ELSE BEGIN SET @cmd = 'del ' + @physicaldevicename PRINT 'Starting deleting the backup file ' + @cmd EXEC @rc = master.dbo.xp_cmdshell @cmd IF @rc <> 0 BEGIN PRINT 'Backup File ' + @physicaldevicename + ' was not deleted' END ELSE BEGIN PRINT 'Backup File ' + @physicaldevicename + ' deleted successfully at timestamp '+ CONVERT(VARCHAR,GETDATE(),109) END ENDENDCLOSE @cursorDEALLOCATE @cursorGOsp_configure 'xp_cmdshell', 0GORECONFIGUREGOsp_configure 'show advanced options', 0GOPRINT 'Running reconfigure'RECONFIGUREGOSET NOCOUNT OFFThanks & Regards,Manjunath C Bhat.You can aslo subscribe to my blog. :)[url=http://manjunathcbhat.blogspot.com][/url]

RE: Delete old Backup files using T-sql script

SKYBVI — Thu, 01 Jul 2010 10:41:22 GMT

Please check the maintenance plan. Edit the maintennace cleanup task and see the folder u r tryin to delete it from.is it correct ,also check hte extension and select the option delete files older than the follwing.....specify days...After doing this, then also if it doesnt clean back up files, thendelete the maintenance plan and create it again....It helped me...

RE: Delete old Backup files using T-sql script

fbu3604 — Mon, 28 Jun 2010 12:57:51 GMT

I set up a clean up task through the maintenance plan in SQL 2005 standard version.When I execute it keeps on failing with below error,MessageExecuted as user: COMP\DBSServiceDX1. Microsoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:31:24 PM Could not create DTS.Application because of error 0x80070005It seems to me that I can't create a cleanup task through the Maintenance plan.

RE: Delete old Backup files using T-sql script

fbu3604 — Mon, 28 Jun 2010 12:56:13 GMT

RE: Delete old Backup files using T-sql script

salahudeen-842794 — Thu, 24 Jun 2010 21:03:21 GMT

Hi, You can try the below link for SQL Server 2000.http://www.mssqltips.com/tip.asp?tip=1324SQL Server 2005 have different script to delete the backup file as the below. Save this script as a .sql and call the file from batch file. The batch files are given below.=========================DECLARE @DeleteFiles NVARCHAR(MAX)DECLARE DeleteFile CURSORFORSELECT 'exec xp_cmdshell ''DEL "' + physical_device_name + '"''' +CHAR(13)+CHAR(10)FROM msdb.dbo.backupmediafamily msJOIN msdb.dbo.backupset us ON ms.media_set_id = us.media_set_id WHERE backup_finish_date < GETDATE() And backup_finish_date > GETDATE()-2 AND Type ='D' order by backup_finish_date desc OPEN DeleteFileFETCH NEXT FROM DeleteFile INTO @DeleteFiles WHILE @@FETCH_STATUS = 0 BEGIN EXEC (@DeleteFiles) print(@DeleteFiles) FETCH NEXT FROM DeleteFile INTO @DeleteFiles END CLOSE DeleteFileDEALLOCATE DeleteFile======================Batch file.-- Change it 2 HOSTNAME words for the actual name of the MSSQL Instance name.-- Check the below paths are correct for the server and change if needed.sqlcmd -S <instancename> -d msdb -E -i d:\mssql\dba\sql\delete_backups.sql -o d:\mssql\dba\reports\delete_backup_report.txt=================call the batch file from the schedule task.

RE: Delete old Backup files using T-sql script

amarokmusic — Fri, 19 Mar 2010 03:36:03 GMT

it's true is kind of rubbish because it can't handle multiple paths. The Maintenance plan executes a command similar to the one below:xp_delete_file 0,'+@backuppath+',N''bak'','+@DeleteDate+',1@backuppath speaks for itself @DeleteDate is the threshold date beyond which anything is deleted expressed by convert(varchar(50), CAST(getdate() AS datetime),126)

RE: Delete old Backup files using T-sql script

john.campbell-1020429 — Thu, 18 Mar 2010 06:59:35 GMT

Here's how I do it with a sp. The path can be local or remote. Here days = 3, all "bak" files in the directory older that 3 days will be deleted.You can run the commands as a sp, or can fold into a bat file and run on the server with a bat file and schedule with windows schedulerJohn.ALTER procedure [dbo].[jc_deleteBackup]as-- need to set the path and the number of daysdeclare @cmd varchar(2000)set @cmd = 'forfiles /p c:\sqlbackups /d -3 -S -m *.bak -C "cmd /c del @file"'exec master..xp_cmdshell @cmd

RE: Delete old Backup files using T-sql script

irving.drake — Wed, 17 Mar 2010 08:55:40 GMT

Prueba este código debe servirte, para lo que preguntas.DECLARE @exitcode intDECLARE @sqlerrorcode intEXECUTE master..sqlbackup N'-SQL "BACKUP DATABASES [master, model, msdb] TO DISK = ''C:\BKSQL\<AUTO>.sqb'' WITH COMPRESSION = 2, FILECOUNT = 2, ERASEFILES_ATSTART = 6,"', @exitcode OUT, @sqlerrorcode OUTIF (@exitcode >= 500) OR (@sqlerrorcode <> 0)BEGINRAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)END

RE: Delete old Backup files using T-sql script

Brandon Forest — Thu, 19 Nov 2009 14:17:41 GMT

Mark,Thanks for the tip. I usually use a vbscript with the filesystem object to handle these types of tasks. I never knew of the ForFiles command-line utility. I checked it out at: http://technet.microsoft.com/en-us/library/cc753551(WS.10).aspx . Because it is still officially supported, using this in a job as a command-line task would be would be better than using the undocumented xp_delete_file T-SQL extended stored procedure. I also avoid using xp_cmdshell for obvious security reasons.Brandon Forest

RE: Delete old Backup files using T-sql script

Mark_Pratt — Thu, 19 Nov 2009 09:11:29 GMT

Without site of the code or a screenshot of the maintenance plan, we are all just guessing as to why the files are not being deleted. Perhaps the poster can upload something ...We all seem to be going [i]round the houses[/i] just to delete a few files ... The sample code below can be run as a cmd file from a schedule task and contain multiple rows for different file types and/or locations.Forfiles.exe is installed by default on 2003 or available in the Windows 2000 Resource Kit.[code="plain"]@echo offForfiles /p Y:\Backups\Database /s /m *.bak /d -2 /c "Cmd /C del /Q @file"C:\>forfiles /?FORFILES [/P pathname] [/M searchmask] [/S] [/C command] [/D [+ | -] {dd/MM/yyyy | dd}]Description: Selects a file (or set of files) and executes a command on that file. This is helpful for batch jobs.Parameter List: /P pathname Indicates the path to start searching. The default folder is the current working directory (.). /M searchmask Searches files according to a searchmask. The default searchmask is '*' . /S Instructs forfiles to recurse into subdirectories. Like "DIR /S". /C command Indicates the command to execute for each file. Command strings should be wrapped in double quotes. The default command is "cmd /c echo @file". The following variables can be used in the command string: @file - returns the name of the file. @fname - returns the file name without extension. @ext - returns only the extension of the file. @path - returns the full path of the file. @relpath - returns the relative path of the file. @isdir - returns "TRUE" if a file type is a directory, and "FALSE" for files. @fsize - returns the size of the file in bytes. @fdate - returns the last modified date of the file. @ftime - returns the last modified time of the file. To include special characters in the command line, use the hexadecimal code for the character in 0xHH format (ex. 0x09 for tab). Internal CMD.exe commands should be preceded with "cmd /c". /D date Selects files with a last modified date greater than or equal to (+), or less than or equal to (-), the specified date using the "dd/MM/yyyy" format; or selects files with a last modified date greater than or equal to (+) the current date plus "dd" days, or less than or equal to (-) the current date minus "dd" days. A valid "dd" number of days can be any number in the range of 0 - 32768. "+" is taken as default sign if not specified. /? Displays this help message.Examples: FORFILES /? FORFILES FORFILES /P C:\WINDOWS /S /M DNS*.* FORFILES /S /M *.txt /C "cmd /c type @file | more" FORFILES /P C:\ /S /M *.bat FORFILES /D -30 /M *.exe /C "cmd /c echo @path 0x09 was changed 30 days ago" FORFILES /D 01/01/2001 /C "cmd /c echo @fname is new since Jan 1st 2001" FORFILES /D +19/11/2009 /C "cmd /c echo @fname is new today" FORFILES /M *.exe /D +1 FORFILES /S /M *.doc /C "cmd /c echo @fsize" FORFILES /M *.txt /C "cmd /c if @isdir==FALSE notepad.exe @file"C:\>[/code]

RE: Delete old Backup files using T-sql script

muthukkumaran Kaliyamoorthy — Thu, 19 Nov 2009 07:20:22 GMT

Salom Rangel, Yes u r right.Its better to turn off xp_cmdshell after using.

RE: Delete old Backup files using T-sql script

Ignacio A. Salom Rangel — Thu, 19 Nov 2009 06:54:26 GMT

I run my whole backup and DR scripts with SSIS and so far I have little problems with it. It makes the maintenance of the scripts a lot easier that TSQL scripts.

RE: Delete old Backup files using T-sql script

Ignacio A. Salom Rangel — Thu, 19 Nov 2009 06:48:33 GMT

You can add the sp_configure to the script to enable and disable the xp_cmdshell and then you are set.

RE: Delete old Backup files using T-sql script

muthukkumaran Kaliyamoorthy — Thu, 19 Nov 2009 04:39:48 GMT

[quote][b]sreeni .r.julakanti (4/1/2008)[/b][hr]Guys do any one have script to delete old backup files more than 5 days worth using T-SQL Code,if so please post .because i am looking the ways to delete old files in folders.[/quote]HiCheck the following link my friend wrote a custom script. [url]http://www.sqlservercentral.com/scripts/Administration/68440/[/url]

RE: Delete old Backup files using T-sql script

Brandon Forest — Wed, 18 Nov 2009 14:57:31 GMT

First of all Serena's question was how to do it, not why. All answers making a judgment on why and suggesting something else are irrelevant. That being said I have several reasons why I'm moving away from maintenance plans back to T-SQL run from jobs.1) My maintenance plans are failing on a regular basis because of poor error handling.2) Using maintenance plans requires having SSIS up and running, which is another possible point of failure.3) See Ola Hallengren's comparison chart here: http://ola.hallengren.com/MaintenancePlans.html 4) A Microsoft Premier 3rd level SQL Server support technician agrees with my assessment that maintenance plans do more harm than good.Maintenance plans simplify the steps in backing up logs & databases, checking database integrity, shrinking databases, and other mundane chores for a DBA, but that doesn't mean that simple is better. Since the advent of TRY/CATCH error handling in SQL Server 2005 I say that it is better to code these routines and handle the errors in a better manner than just looking at the Job history. I may be opening up myself to flames for taking this position, but I will say that it is better for a DBA to code his own maintenance routines with T-SQL in Jobs than it is to use the Maintenance Plan Wizard. I'm open constructive arguments either way. :-PBrandon_Forest@sbcglobal.net

RE: Delete old Backup files using T-sql script

Ed Zann — Tue, 14 Jul 2009 12:13:34 GMT

If you are using the SQL Agent you can also use VBScript:==========================================================Option Expliciton error resume next Dim oFSO Dim sDirectoryPath Dim oFolder Dim oFileCollection Dim oFile Dim iDaysOld'Customize values here to fit your needs iDaysOld = 5 Set oFSO = CreateObject("Scripting.FileSystemObject") sDirectoryPath = "Set backup file path here" set oFolder = oFSO.GetFolder(sDirectoryPath) set oFileCollection = oFolder.Files'Walk through each file in this folder collection. For each oFile in oFileCollection If oFile.DateLastModified < (Date() - iDaysOld) Then oFile.Delete(True) End If Next'Clean up Set oFSO = Nothing Set oFolder = Nothing Set oFileCollection = Nothing Set oFile = Nothing

RE: Delete old Backup files using T-sql script

Abhijit More — Mon, 13 Jul 2009 07:39:21 GMT

What is the error you are getting?

RE: Delete old Backup files using T-sql script

aks_osd — Mon, 22 Jun 2009 09:19:48 GMT

Declare @sql varchar(250),@retention tinyintset @retention=2set @sql='Exec master.dbo.xp_cmdshell ''DIR D:\sql_backup\*'+ substring(convert(varchar(15),getdate(),104),7,4) + substring(convert(varchar(15),getdate(),104),4,2)+substring(convert(varchar(15),getdate()-@retention,104),1,2) +'*.bak /s'''print @sqlExec (@sql)

RE: Delete old Backup files using T-sql script

aks_osd — Mon, 22 Jun 2009 09:17:18 GMT

RE: Delete old Backup files using T-sql script

franky.Piferoen — Thu, 06 Nov 2008 06:29:56 GMT

Hi,maybe you could use xp_delete_file, but this is undocumented.Fe EXECUTE master.dbo.xp_delete_file 0,N'C:\Backup',N'BAK',N'2008-08-30T07:49:27',1First param : 0= backupfiles, 1 = reportfiles2nd param : Path3 th param : extension of the files you want to delete4th param : all files older than this date will be deleted5th param : include subdirs or not

RE: Delete old Backup files using T-sql script

The_SQL_DBA — Sat, 23 Aug 2008 07:51:27 GMT

This should work fine for you.Once you create this procedure, just go ahead and create a job and include the step as exec usp_DeleteOldBackupFiles . The code is shown below use DBgoif( object_id('usp_DeleteOldBackupFiles') is not null ) drop PROCEDURE dbo.usp_DeleteOldBackupFilesGOCREATE PROCEDURE dbo.usp_DeleteOldBackupFiles @basedir nvarchar(255), @days_old_to_allow int = 30ASbegin set nocount on declare @mtime datetime declare @file nvarchar(255) declare @fullpath nvarchar(255) declare @daysold int declare @cmd nvarchar(255) create table #t_dir ( InLine varchar(150) ) -- get a directory listing set @cmd = 'dir "' + @basedir + '" /A-D' -- /A-D, no directories insert into #t_dir exec master.dbo.xp_cmdshell @cmd-- insert into #t_dir-- select * from tmpintable delete from #t_dir where InLine like ' %' or InLine = '' or InLine like '% %' declare c_files cursor for select convert(datetime, substring(replace(replace(replace(InLine, ' ', '~'), ' ', ''), '~', ' '),1,17) + 'm') as dtime, rtrim(substring(InLine, 40, len(InLine))) as filen, datediff(dd, convert(datetime, substring(replace(replace(replace(InLine, ' ', '~'), ' ', ''), '~', ' '),1,17) + 'm') , getdate() ) as daysold from #t_dir where datediff(dd, convert(datetime, substring(replace(replace(replace(InLine, ' ', '~'), ' ', ''), '~', ' '),1,17) + 'm') , getdate() ) > @days_old_to_allow open c_files fetch next from c_files into @mtime, @file, @daysold while(@@fetch_status = 0) begin set @fullpath = @basedir + '\' + @file print 'Going to delete old file: ' + @fullpath + ', daysold=' + cast(@daysold as nvarchar) set @cmd = 'del /Q "' + @fullpath + '"' print @cmd -- no turning back now! exec master.dbo.xp_cmdshell @cmd, no_output fetch next from c_files into @mtime, @file, @daysold end close c_files deallocate c_files drop table #t_direndGO/*usp_DeleteOldBackupFiles 'c:\mssql\backup', -1*/go

RE: Delete old Backup files using T-sql script

Jason Tontz — Fri, 22 Aug 2008 15:37:27 GMT

These are all great suggestions. I found this article on this site by Robert Pearl. http://www.sqlservercentral.com/articles/Administration/2953/He has included a script along with a really great description of our disappointment in this issue (thanks Robert). So he has this script and also talks about maintenance Plans in SP2(a). If you have it already, try using the last choice on the list of Maintenance Plans called "Maintenance Cleanup Task". This is a far cry from what we had in 2000. In our environment we need 1 job to delete .BAK (backups) and another for .TRN (Tranaction Logs). Don't forget about a 3rd for the Backup Log Files (*.txt) and a 4th for the actual backup history entry in MSDB which is a different maintenance plan option called "Clean Up History".Try not to have too much fun creating 4 jobs to cleanup after your 1 backup job. And I do mean that in an "Eddy Haskel" kinda way.:)Peace.

RE: Delete old Backup files using T-sql script

Ola Hallengren — Fri, 18 Apr 2008 09:39:35 GMT

If you just need to delete backup files from inside T-SQL I would use a solution based on xp_delete_file.Test the command below. It means that you are deleting all backup files older than the specified date and time in the specified directory.EXECUTE xp_delete_file 0, '\\Computer\Share', 'bak', '2008-04-18T00:00:00'If this works you could easily build some T-SQL so that it is deleting backup files that are older than a number of days.Ola Hallengren[url]http://ola.hallengren.com[/url]

RE: Delete old Backup files using T-sql script

nivas — Fri, 18 Apr 2008 09:07:00 GMT

Actually i am using UNC Path, however i tried again today but this time it is asking comformation like are you sure yfor this case what i have to do.advise

RE: Delete old Backup files using T-sql script

nivas — Fri, 18 Apr 2008 09:05:18 GMT

i didnot find any script or solution from this link you provided.sorry

RE: Delete old Backup files using T-sql script

Ola Hallengren — Fri, 18 Apr 2008 02:46:21 GMT

The Maintenance Plans are using the extended stored procedure xp_delete_file to delete files. That can also be used in T-SQL code.EXECUTE xp_delete_file 0, 'C:\Backup', 'bak', '2008-04-18T00:00:00'I have a backup stored procedure that is using xp_delete_file that you can use if you like.[url]http://ola.hallengren.com/sql-server-backup.html[/url]Ola Hallengren[url]http://ola.hallengren.com[/url]

RE: Delete old Backup files using T-sql script

alee-652255 — Thu, 17 Apr 2008 15:30:07 GMT

Do not use mapped drive path, use UNC path.

RE: Delete old Backup files using T-sql script

nivas — Thu, 17 Apr 2008 14:53:58 GMT

Hi in this case my date format used in backup file getutcdate() format but to delete files i am using this script Declare @sql varchar(250),@retention tinyintset @retention=0set @sql='Exec master.dbo.xp_cmdshell ''Del E:\SQL2005\Backup\*' + convert(varchar(15),getdate()-@retention,120) + '*.bak'''Exec (@sql)i also tried with getutcdate() but it gives the following error.Could Not Find \\E:\SQL2005\Backup\*2008-04-03*.bakwhy i donot understand.can any one give heads up.

RE: Delete old Backup files using T-sql script

krammana — Fri, 04 Apr 2008 08:13:10 GMT

You Can use this Extended stored procedure for that....[b]master.dbo.xp_delete_file [/b]

RE: Delete old Backup files using T-sql script

Ivanna Noh — Thu, 03 Apr 2008 15:38:14 GMT

have you checked to ensure that the owner of the maintenance job (the identity that it runs under) has delete permission on the server folder where the backups are stored?

RE: Delete old Backup files using T-sql script

alee-652255 — Thu, 03 Apr 2008 10:23:50 GMT

This is how I keep backup for seven days:Use MasterDeclare@new_device nvarchar(40),@new_file nvarchar(40),@old_device nvarchar(40)set @new_device='MyDB'+convert(char(8),getdate(),12)set @new_file='d:\mssql\BACKUP\MyDB'+convert(char(8),getdate(),12)set @old_device='MyDB'+convert(char(8),(getdate()-7),12)EXEC sp_addumpdevice 'disk',@new_device,@new_fileBACKUP DATABASE MyDB TO @new_deviceIf exists (Select name from sysdevices where name=@old_device)EXEC sp_dropdevice @old_device,delfile