backup files delete - script

• ALIF-662928

  SSCertifiable

  Points: 6054

  More actions

  August 29, 2011 at 2:58 pm

  #242963

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

  More actions

  August 29, 2011 at 4:50 pm

  #1374770

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

  More actions

  August 30, 2011 at 5:55 am

  #1374904

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

  More actions

  August 30, 2011 at 7:47 am

  #1375006

  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

  More actions

  August 30, 2011 at 8:43 am

  #1375043

  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