Technical Article

Delete old backup files

,

This is one example of how you can make backups with a date stamp for the file name. You can then periodically remove the files that are older than a specified date by simply placing a few lines of code in a job step and calling the supplied sp and passing the date from which to delete. This script can easily be modified to suite each individuals needs and they can then get away from the problems associated with the maintenance plan.

----------  Do a backup and create a separate file for each day of the week  ----------------

DECLARE @DBName NVARCHAR(50), @Device NVARCHAR(100), @Name NVARCHAR(100)

IF OBJECT_ID('tempdb..#DBs') IS NOT NULL
    DROP TABLE #DBs

CREATE TABLE #DBs ([name] VARCHAR(50),[db_size] VARCHAR(20),
                [Owner] VARCHAR(20),[DBID] INT, [Created] VARCHAR(14), 
                [Status] VARCHAR(1000), [Compatibility_Level] INT)

INSERT INTO #DBs EXEC sp_helpdb


DECLARE cur_DBs CURSOR STATIC LOCAL 
FOR SELECT [Name] 
        FROM #DBs
            WHERE [DBID] IN (5,6)

OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @Device = N'C:\Backups\DD_' + @DBName + '_Full_' + 
            CAST(DAY(GETDATE()) AS NVARCHAR(4)) + 
            CAST(MONTH(GETDATE()) AS NVARCHAR(4)) + 
            CAST(YEAR(GETDATE()) AS NVARCHAR(8)) + N'.BAK' 

    SET @Name = @DBName + N' Full Backup'

    BACKUP DATABASE @DBName TO DISK = @Device WITH  INIT ,  NOUNLOAD , 
                 NAME = @Name,  NOSKIP ,  STATS = 10,  NOFORMAT

    RESTORE VERIFYONLY FROM DISK = @Device  WITH FILE = 1
    
    FETCH NEXT FROM cur_DBs INTO @DBName 
END

CLOSE cur_DBs
DEALLOCATE cur_DBs

-------------------------------------------------------------------------
----    Removing Older Backup Files    -------------------

DECLARE @Error INT, @D DATETIME
SET @D = CAST('20020801 15:00:00' AS DATETIME)

EXEC @Error = remove_old_log_files @D

SELECT @Error
--------------------------------------------------------------------------
----  ***   Procedure to remove old backups   ****  ------

CREATE PROCEDURE remove_old_log_files 
@DelDate DATETIME

AS

SET NOCOUNT ON

DECLARE @SQL VARCHAR(500), @FName VARCHAR(40), @Error INT
DECLARE @Delete VARCHAR(300), @Msg VARCHAR(100), @Return INT

SET DATEFORMAT MDY


IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
    DROP TABLE #DirList

CREATE TABLE #dirlist (FName VARCHAR(1000))

CREATE TABLE #Errors (Results VARCHAR(1000))

--  Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName) 
        exec master..xp_cmdshell 'dir /OD C:\Backups\*.trn'

SET @Error = @@ERROR
IF @Error <> 0
BEGIN
    SET @Msg = 'Error while getting the filenames with DIR ' 
    GOTO On_Error
END
--SELECT * FROM #dirList

--  Remove the garbage
DELETE #dirlist WHERE 
    SUBSTRING(FName,1,2) < '00' OR
    SUBSTRING(FName,1,2) > '99' OR
    FName IS NULL


--  Create a cursor and for each file name do the processing.
--  The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR 
    SELECT  SUBSTRING(FName,40,40) AS FName
        FROM #dirlist
            WHERE  CAST(SUBSTRING(FName,1,20) AS DATETIME) <  @DelDate
                AND SUBSTRING(FName,40,40) LIKE '%.TRN'

OPEN curDir

FETCH NEXT FROM curDir INTO @Fname
WHILE (@@fetch_status = 0)
BEGIN


    -- Delete the old backup files
    SET @Delete = 'DEL "C:\Backups\' + @FName + '"'

    INSERT INTO #Errors (Results) 
        exec master..xp_cmdshell @Delete
               

    IF @@RowCount > 1
    BEGIN
        SET @Error = -1
        SET @Msg = 'Error while Deleting file ' + @FName
        GOTO On_Error
    END


--    PRINT @Delete
    PRINT 'Deleted ' + @FName + '  at ' +  CONVERT(VARCHAR(28),GETDATE(),113)

FETCH NEXT FROM curDir INTO @Fname
END

CLOSE curDir
DEALLOCATE curDir

DROP TABLE #DirList
DROP TABLE #Errors

RETURN @Error

On_Error:
BEGIN
    IF @Error <> 0
    BEGIN
        SELECT @Msg + '.  Error # ' + CAST(@Error AS VARCHAR(10))
        RAISERROR(@Msg,12,1)
        RETURN @Error
    END
END
GO

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating