April 1, 2008 at 3:02 pm
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.
April 1, 2008 at 3:43 pm
Can't you do it with Maintenance Plans?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 1, 2008 at 4:04 pm
Search here in the scripts section. There are lots of backup scripts that can probably do this.
Note that they will require the xp_cmdshell for the most part, and that's not necessarily what you want to do. A simple Maintenance plan can do this using the Maintenance Cleanup task.
April 1, 2008 at 8:30 pm
I've noticed that when I backup to an external drive, the maintanence plan will not delete files. This is in SQL Server 2000. Not sure what the problem is, so I had to create a .bat script and call it from xp_cmdshell.
April 1, 2008 at 10:38 pm
Maintenance plans delete backup files for me on SQL 2000.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 1, 2008 at 10:55 pm
If I run backups to a network share or on the local drive, I have no issues of having them delete the backups. It just happens when I backup to an external drive. I backup to a 500 GB external hard drive for DRP. I use a .bat script to go out and delete any files older than 7 days.
April 2, 2008 at 6:26 am
On 2003 I used the forfiles utility via xp_cmdshell to list the files and properties and then load this information into a table. I do a compare of the physical files against the logical history and then decide if it is safe to drop the old backups.
Nigel Moore
======================
April 2, 2008 at 9:09 am
I created maintenance job that job never deletes FAK Files but it shows success,
i did not get why it is so.
April 2, 2008 at 9:09 am
Maintanence Job is not working for me .
April 2, 2008 at 4:48 pm
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... 🙂
April 3, 2008 at 10:23 am
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
April 3, 2008 at 3:38 pm
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?
April 4, 2008 at 8:13 am
You Can use this Extended stored procedure for that....master.dbo.xp_delete_file
Viewing 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply