Delete old Backup files using T-sql script
-
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.
-
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] -
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.
-
I agree with Steve! Why don't you use a maintenance plan?
-
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.
-
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] -
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.
-
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
====================== -
I created maintenance job that job never deletes FAK Files but it shows success,
i did not get why it is so.
-
Maintanence Job is not working for me .
-
Which version of SQL Server are you running?
-
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... 🙂
-
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
-
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?
-
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