April 17, 2008 at 2:53 pm
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 tinyint
set @retention=0
set @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*.bak
why i donot understand.
can any one give heads up.
April 17, 2008 at 3:30 pm
Do not use mapped drive path, use UNC path.
April 18, 2008 at 2:46 am
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.
http://ola.hallengren.com/sql-server-backup.html
Ola Hallengren
April 18, 2008 at 9:05 am
i didnot find any script or solution from this link you provided.
sorry
April 18, 2008 at 9:07 am
Actually i am using UNC Path, however i tried again today but this time it is asking comformation like are you sure y
for this case what i have to do.
advise
April 18, 2008 at 9:39 am
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
August 22, 2008 at 3:37 pm
These are all great suggestions. I found this article on this site by Robert Pearl.
http://qa.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.
August 23, 2008 at 7:51 am
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 DB
go
if( object_id('usp_DeleteOldBackupFiles') is not null )
drop PROCEDURE dbo.usp_DeleteOldBackupFiles
GO
CREATE PROCEDURE dbo.usp_DeleteOldBackupFiles
@basedir nvarchar(255),
@days_old_to_allow int = 30
AS
begin
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_dir
end
GO
/*
usp_DeleteOldBackupFiles 'c:\mssql\backup', -1
*/
go
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
November 6, 2008 at 6:29 am
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',1
First param : 0= backupfiles, 1 = reportfiles
2nd param : Path
3 th param : extension of the files you want to delete
4th param : all files older than this date will be deleted
5th param : include subdirs or not
June 22, 2009 at 9:17 am
Declare @sql varchar(250),@retention tinyint
set @retention=2
set @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 @sql
Exec (@sql)
June 22, 2009 at 9:19 am
Declare @sql varchar(250),@retention tinyint
set @retention=2
set @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 @sql
Exec (@sql)
July 13, 2009 at 7:39 am
What is the error you are getting?
Abhijit - http://abhijitmore.wordpress.com
July 14, 2009 at 12:13 pm
If you are using the SQL Agent you can also use VBScript:
==========================================================
Option Explicit
on 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
November 18, 2009 at 2:57 pm
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. 😛
November 19, 2009 at 4:39 am
sreeni .r.julakanti (4/1/2008)
Guysdo 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.
Hi
Check the following link my friend wrote a custom script.
http://qa.sqlservercentral.com/scripts/Administration/68440/
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply