backup files delete - script

  • ALIF-662928

    SSCertifiable

    Points: 6054

    Hi,

    I need to clean backups and the condition is

    to delete all bak files in the folder but keeping 1 (or 2) the most recent ones regardless of time they were created.

  • MyDoggieJessie

    SSC-Forever

    Points: 44278

    Not the best approach (using xp_cmdshell) but you could try something like this (it should work just fine, provided your proxy account is able to delete files off the file system where the files are located):

    Just change the @Location1 path variables, add as many dbs as needed to the #DBs table and you should be good to go!

    SET NOCOUNT ON

    GO

    /* Variable declarations */

    DECLARE @DBName sysname, @Idx int, @SQL nvarchar(500), @FileName varchar(125)

    DECLARE @Location1 nvarchar(100) = '\\server\folder\etc\', @Location2 nvarchar(100)

    SET @Location2 = @Location1 --<< used because we want to preserve the original file location (used below)

    /* Create Tables */

    CREATE TABLE #DBs (

    iDx int IDENTITY(1,1), TName varchar(25), Used tinyint

    )

    CREATE TABLE #OriginalFileList (

    Col1 varchar(1000) NULL

    )

    CREATE TABLE #ParsedFileList (

    PFLID INT PRIMARY KEY IDENTITY (1,1) NOT NULL,

    DateTimeStamp datetime NOT NULL,

    FileSize varchar(150) NOT NULL,

    FileName1 varchar (255) NOT NULL

    )

    /* Populate table - this assumes your backup folder locations have the same name as the database itself */

    INSERT INTO #DBs (TName, Used) VALUES ('msdb', 0)

    INSERT INTO #DBs (TName, Used) VALUES ('MyCoolDB1', 0)

    INSERT INTO #DBs (TName, Used) VALUES ('MyCoolerDB2', 0)

    INSERT INTO #DBs (TName, Used) VALUES ('MyCoolestDB3', 0)

    WHILE EXISTS (SELECT TOP 1 Idx FROM #DBs)

    BEGIN

    SELECT @DBName = Tname, @Idx = iDx FROM #DBs WHERE Used = 0

    SET @Location2 = RTRIM(@Location1) + RTRIM(@DBName)

    SELECT @SQL = 'master.dbo.xp_cmdshell ' + char(39) + 'dir ' + @Location2 + '\*.bak' + char(39)

    SELECT @SQL = 'INSERT INTO #OriginalFileList(Col1)' + char(13) + 'EXEC ' + @SQL

    EXEC (@SQL)

    SET @Location2 = @Location1

    DELETE FROM #DBs WHERE iDx = @Idx

    END

    /* Delete unneeded data from the #OriginalFileList */

    DELETE FROM #OriginalFileList WHERE COL1 IS NULL

    DELETE FROM #OriginalFileList WHERE COL1 LIKE '%Volume%'

    DELETE FROM #OriginalFileList WHERE COL1 LIKE '%Directory%'

    DELETE FROM #OriginalFileList WHERE COL1 LIKE '%<DIR>%'

    DELETE FROM #OriginalFileList WHERE COL1 LIKE '%bytes%'

    /* Populate with information from the file system */

    INSERT INTO #ParsedFileList (DateTimeStamp, FileSize, FileName1)

    SELECT

    CAST(DATEDIFF(dd,0 ,LTRIM(SUBSTRING (Col1, 1, 20))) AS DATETIME) AS 'DateTimeStamp',

    LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',

    LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'

    FROM #OriginalFileList

    WHERE CAST(DATEDIFF(dd,0 ,LTRIM(SUBSTRING (Col1, 1, 20))) AS DATETIME) = CAST(DATEDIFF(dd, 1 ,GETDATE()) AS DATETIME)

    ORDER BY DateTimeStamp DESC

    WHILE EXISTS (SELECT TOP 1 PFLID FROM #ParsedFileList WHERE PFLID > 3)

    BEGIN

    SELECT @Idx = PFLID, @FileName = FileName1 FROM #ParsedFileList WHERE PFLID > 3

    SET @SQL = 'master.dbo.xp_cmdshell del ' + RTRIM(@Location1) + @FileName

    EXEC (@SQL)

    DELETE FROM #ParsedFileList WHERE PFLID = @Idx

    SET @Idx = @Idx + 1

    END

    /* Send Email to let Developers know it's ready */

    EXEC msdb.dbo.sp_send_dbmail

    @recipients = 'me@mycompany.com',

    @copy_recipients = 'dba_alerts@mycompany.com',

    @subject = 'SERVER :: Top 3 databases remain, all others have been permanently purged',

    @body = 'Blah, blah, blah...add message here',

    @importance = 'High'

    /* Tidy up */

    DROP TABLE #OriginalFileList

    DROP TABLE #ParsedFileList

    DROP TABLE #DBs

    SET NOCOUNT OFF

    GO

    ______________________________________________________________________________
    Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Perry Whittle

    SSC Guru

    Points: 233859

    Hi

    use this and execute from a sql server agent job step 😎

    Option Explicit

    'Delete all SQL Server backup files more than 8 days old

    Dim oFS, oSQLBackupFol, oFol, oFil

    Set oFS = CreateObject("Scripting.FileSystemObject")

    Set oSQLBackupFol = oFS.GetFolder("D:\MSSQL.1\MSSQL\Backup") 'Change this as appropriate

    For Each oFol IN oSQLBackupFol.SubFolders

    For Each oFil in oFol.Files

    If oFil.DateCreated < Now-8 then 'Change this as appropriate

    If ucase(right(oFil.name, 4)) = ".BAK" then 'change extension as required

    oFil.Delete

    End If

    End If

    Next

    Next

    set oFS = nothing

    set oSQLBackupFol = nothing

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • MyDoggieJessie

    SSC-Forever

    Points: 44278

    It's always neat to see the different ways things can be efficiently accomplished. I like your script Perry! Simple.

    ______________________________________________________________________________
    Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • ALIF-662928

    SSCertifiable

    Points: 6054

    Thanks Guys,

    I think i got exactly what i needed, i wanted to leave the latest file kept untouched and not delete it, doesnt matter what date the latest file was(1 day, 10 days or 1 months old),

    It worked out for me when i ran this .bat script from the sql agent which is as follows...

    @echo off

    cls

    setlocal

    For /f "skip=2" %%a in ('Dir "\\Servername\w$\Backup\" /B /O:-N /T:C') do del "\\Servername\w$\Backup\%%a" /q /S

    Endlocal

    Exit

    Skip=2 will keep the latest 2 days of file and delete everything inside, we can change how many latest files to skip as per the above script and run using the cmdExec.

    Thanks

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply