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