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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy