June 27, 2012 at 1:20 pm
Comments posted to this topic are about the item Delete old backup
July 9, 2012 at 10:18 pm
I dont think we need this much big line of code to delete a file.
I have one SP which need the input parameter as fullpath+filename and will delete the file.
The code is as below :-
USE [master]
GO
/****** Object: StoredProcedure [dbo].[delfile] Script Date: 07/10/2012 14:15:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create proc [dbo].[delfile] @filename varchar(200)
--please ensure you give full path and filename which need to be deleted. For Ex. d:\test\test.bak is valid input.
as
declare @sql varchar(200)
set @sql = 'xp_cmdshell '+''''+'del '+@filename +''''
print 'deleting file:-' + @sql
exec(@sql)
----------
Ashish
July 10, 2012 at 12:52 am
Hi,
the code is long because we deleting multi-files without checking their names,but just knowing the folder name and how old they are... and it will only delete SQL backup files(.bak,.trn)
the script will delete older backup files and leave the rest...
using your script i think i would have to know the filename, then manual put it on the script to be delete.
January 30, 2013 at 1:52 pm
Hi,
Your script has seemed to me good, although I've made ??some modifications to improve performance, in my case it is not necessary to delete temporary tables at the end of the script as they are automatically deleted when you close the connection to the instance of SQL Server ... however I don't delete that code.
The change also seeks files in subdirectories.
I ask your permission to publish the amended version on a small blog that I'm building ... I can do it? 🙂
regards
-------------------------------------------------------------------------------------------
--Declare all the variables to be used
-------------------------------------------------------------------------------------------
DECLARE
@FileName VARCHAR(100),
@DynDelete VARCHAR(100),
@path VARCHAR(100),
@DifDays VARCHAR (100)= 4, --specify number of days
@Command VARCHAR(255)
BEGIN TRY
-------------------------------------------------------------------------------------------
--CREATE temp TABLEs
-------------------------------------------------------------------------------------------
CREATE TABLE #dir (dir VARCHAR(255))
CREATE TABLE #tobdelted (nameoffile VARCHAR(255),fileDATE DATETIME)
-------------------------------------------------------------------------------------------
--INSERT files found INTO temp TABLE (Please change path)
-------------------------------------------------------------------------------------------
--change the folder path, do not forget the "\" at the end of the path
SET @path= 'D:\backup\'
SET @Command = 'dir /s ' + @path
INSERT INTO #dir EXEC MASTER..xp_cmdshell @Command
-------------------------------------------------------------------------------------------
--INSERT files to be deleted INTO appropriate TABLE
-------------------------------------------------------------------------------------------
INSERT INTO #tobdelted (fileDATE,nameoffile)
SELECT convert(DATE,(SELECT SUBSTRING(dir,1,10)),103),LTRIM(RTRIM(SUBSTRING(dir, LEN(dir)-CHARINDEX(' ',reverse(dir)) + 1,255)))
FROM #dir
WHERE (dir like '%/%%/%') and (dir not like '%<DIR>%') AND
(dir LIKE '%.trn' OR dir like '%.bak')
AND DATEDIFF(DAY, CONVERT(DATE,SUBSTRING(dir,1,10),103), GETDATE()) >= @DifDays
-------------------------------------------------------------------------------------------
--use Curso to delete the files
-------------------------------------------------------------------------------------------
DECLARE curDelFile CURSOR FOR
SELECT[nameoffile]
FROM #tobdelted
OPEN curDelFile
FETCH NEXT FROM curDelFile INTO @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DynDelete = ' DEL '+@path + @FileName + ' /s'
EXEC master..xp_cmdshell @DynDelete
PRINT 'EXEC master..xp_cmdshell'+@DynDelete
FETCH NEXT FROM curDelFile INTO @FileName
END
CLOSE curDelFile
DEALLOCATE curDelFile
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
GOTO END_SCRIPT
END CATCH
END_SCRIPT:
BEGIN TRY
DROP TABLE #dir
DROP TABLE #tobdelted
END TRY
BEGIN CATCH
PRINT 'Tables do not exist'
END CATCH
January 31, 2013 at 6:50 am
The blog I'm building is still small and it is write in Spanish ... but the idea is growing daily.
However gladly will I share the link for that those interested can visit it :-D.
http://recursossqlserver.blogspot.com
May 10, 2016 at 9:16 am
Thanks for the script.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply