Delete old backup files

----------  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