Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Delete old Backup files using T-sql script Expand / Collapse
Author
Message
Posted Wednesday, April 2, 2008 2:51 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:55 PM
Points: 3,084, Visits: 1,431
Which version of SQL Server are you running?




My blog
Post #478850
Posted Wednesday, April 2, 2008 4:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:05 PM
Points: 184, Visits: 1,077
This is a procedure I wrote some time ago to delete files from a fileshare older than n-days.

IF EXISTS (SELECT [name] FROM dbo.sysobjects WHERE [name] = 'usp_Admin_Delete_Files_By_Date' AND TYPE = 'P')
DROP PROCEDURE dbo.usp_Admin_Delete_Files_By_Date
GO

CREATE PROCEDURE dbo.usp_Admin_Delete_Files_By_Date (@SourceDir varchar(1024), @SourceFile varchar(512), @DaysToKeep int)
-- EXEC Admin.dbo.usp_Admin_Delete_Files_By_Date @SourceDir = '\\FooServer\BarShare\'
-- , @SourceFile = 'FooFile_*'
-- , @DaysToKeep = 3

AS

/******************************************************************************
**
** Name: usp_Admin_Delete_Files_By_Date.sql
**
** Description: Delete files older than X-days based on path & extension.
**
** Depending on the output from xp_msver, we will execute either a
** Windows 2000 or Windows 2003 specific INSERT INTO #_File_Details_02
** operation as there is a small difference in the FOR output between
** Windows 2000 and 2003 (Operating system versions).
**
** Return values: 0 - Success
** -1 - Error
**
** Author: G. Rayburn
**
** Date: 03/26/2007
**
** Depends on: xp_cmdshell access to @SourceDir via SQLAgent account.
**
*******************************************************************************
** Modification History
*******************************************************************************
**
** Initial Creation: 03/26/2007 G. Rayburn
**
*******************************************************************************
**
******************************************************************************/
SET NOCOUNT ON

DECLARE @CurrentFileDate char(10)
, @OldFileDate char(10)
, @SourceDirFOR varchar(255)
, @FileName varchar(512)
, @DynDelete varchar(512)
, @ProcessName varchar(150)
, @OSVersion decimal(3,1)
, @Error int


SET @ProcessName = 'usp_Admin_Delete_Files_By_Date - [' + @SourceFile + ']'
SET @CurrentFileDate = CONVERT(char(10),getdate(),121)
SET @OldFileDate = CONVERT(char(10),DATEADD(dd,-@DaysToKeep,@CurrentFileDate),121)
SET @SourceDirFOR = 'FOR %I IN ("' + @SourceDir + @SourceFile + '") DO @ECHO %~nxtI'
SET @Error = 0


-- Get Windows OS Version info for proper OSVer statement block exec.
CREATE TABLE #_OSVersion
( [Index] int
, [Name] varchar(255)
, [Internal_Value] varchar(255)
, [Character_Value] varchar(255) )

INSERT INTO #_OSVersion
EXEC master..xp_msver 'WindowsVersion'

SET @OSVersion = (SELECT SUBSTRING([Character_Value],1,3) FROM #_OSVersion)



-- Start temp table population(s).
CREATE TABLE #_File_Details_01
( Ident int IDENTITY(1,1)
, Output varchar(512) )

INSERT INTO #_File_Details_01
EXEC master..xp_cmdshell @SourceDirFOR

CREATE TABLE #_File_Details_02
(Ident int
, [TimeStamp] datetime
, [FileName] varchar(255) )


-- OS Version specifics.
IF @OSVersion = '5.0'
BEGIN -- Exec Windows 2000 version.
INSERT INTO #_File_Details_02
SELECT Ident
, CONVERT(datetime, LEFT(CAST(SUBSTRING([Output],1,8) AS datetime),12)) AS [TimeStamp]
, SUBSTRING([Output],17,255) AS [FileName]
FROM #_File_Details_01

WHERE [Output] IS NOT NULL
ORDER BY Ident
END

IF @OSVersion = '5.2'
BEGIN -- Exec Windows 2003 version.
INSERT INTO #_File_Details_02
SELECT Ident
, CONVERT(char(10), SUBSTRING([Output],1,10), 121) AS [TimeStamp]
, SUBSTRING([Output],21,255) AS [FileName]
FROM #_File_Details_01

WHERE [Output] IS NOT NULL
ORDER BY Ident
END



-- Start delete ops cursor.
DECLARE curDelFile CURSOR
READ_ONLY
FOR

SELECT [FileName]
FROM #_File_Details_02
WHERE [TimeStamp] <= @OldFileDate

OPEN curDelFile

FETCH NEXT FROM curDelFile INTO @FileName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

SET @DynDelete = 'DEL /Q "' + @SourceDir + @FileName + '"'

EXEC master..xp_cmdshell @DynDelete

END
FETCH NEXT FROM curDelFile INTO @FileName
END

CLOSE curDelFile
DEALLOCATE curDelFile

DROP TABLE #_OSVersion
DROP TABLE #_File_Details_01
DROP TABLE #_File_Details_02
GO



Your friendly High-Tech Janitor... :)
Post #478905
Posted Thursday, April 3, 2008 10:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 1:34 PM
Points: 18, Visits: 60
This is how I keep backup for seven days:

Use Master
Declare
@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_file
BACKUP DATABASE MyDB TO @new_device

If exists (Select name from sysdevices
where name=@old_device)
EXEC sp_dropdevice @old_device,delfile
Post #479366
Posted Thursday, April 3, 2008 3:38 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:03 PM
Points: 830, Visits: 2,463
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?
Post #479596
Posted Friday, April 4, 2008 8:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 9, 2008 1:24 PM
Points: 92, Visits: 117
You Can use this Extended stored procedure for that....master.dbo.xp_delete_file
Post #479999
Posted Thursday, April 17, 2008 2:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 3, 2008 7:53 AM
Points: 17, Visits: 50
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.




Post #486797
Posted Thursday, April 17, 2008 3:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 1:34 PM
Points: 18, Visits: 60
Do not use mapped drive path, use UNC path.
Post #486818
Posted Friday, April 18, 2008 2:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:59 PM
Points: 168, Visits: 1,860
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
http://ola.hallengren.com
Post #486974
Posted Friday, April 18, 2008 9:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 3, 2008 7:53 AM
Points: 17, Visits: 50
i didnot find any script or solution from this link you provided.
sorry
Post #487280
Posted Friday, April 18, 2008 9:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 3, 2008 7:53 AM
Points: 17, Visits: 50
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
Post #487281
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse