August 29, 2011 at 2:58 pm
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.
August 29, 2011 at 4:50 pm
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
August 30, 2011 at 5:55 am
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" 😉
August 30, 2011 at 7:47 am
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
August 30, 2011 at 8:43 am
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply