How to remove old SQL Server Backup Files (.bak) within a script

Question

Post reply

How to remove old SQL Server Backup Files (.bak) within a script

HookSQLDBA7

SSChampion

Points: 10709
More actions
October 13, 2011 at 7:39 am

#249172

We have SQL Server 2005 SP3 running on Windows 2003 Server. I want to remove my backups from the Maintenance Plan (GUI) and use transact-sql scripts to perform the backups. I have the following script (which works) that takes a full backup and puts a date/time stamp on the .bak file.
--Backup the TestDB Database.
USE master
GO
DECLARE @TimeStamp char(17)
SET @TimeStamp = (SELECT REPLACE(CONVERT(varchar(10),getdate(),112),'/','')) + (SELECT REPLACE(CONVERT(varchar(12),getdate(),108),':',''))
DECLARE @FileName char(75)
SET @FileName = 'D:\Backups\Databases\TestDB_' + Substring(@TimeStamp,1,12) + '.bak'
BACKUP DATABASE TestDB TO
DISK= @FileName
WITH INIT, RETAINDAYS = 7;
I want to keep each backup file for 7 days. After 7 days I would like to remove the physical (.bak) file. (I am not sure about the RETAINDAYS Option I think it makes the backup file invalid after 7 days or expire it after 7 days but still keeps the physical file in the directory.) Currently, the backup files older than 7 days are being removed with a Clean Up Task within the Mainenance Plan. I am ony using SQL Server 2005, I do not have any third party Backup/Recovery Tools. I do have access to XCOPY Dos Command, maybe I could set up a batch job to remove older files from the backup directory. I was hoping I could just use SQL Server. Any advice is appreciated.
Thanks, Kevin

Viewing 7 posts - 1 through 6 (of 6 total)

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

Perry Whittle SSC Guru Points: 233850 More actions · Answer 1

i use a simple VBScript to prune files

Option Explicit

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

Dim oFS, oSQLBackupFol, oFol, oFil

Set oFS = CreateObject("Scripting.FileSystemObject")

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

For Each oFol IN oSQLBackupFol.SubFolders

For Each oFil in oFol.Files

If oFil.DateCreated < Now-8 AND Ucase(right(oFil.name, 3)) = "BAK" then

oFil.Delete

End If

Next

set oFol = nothing

set oSQLBackupFol = nothing

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

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

HookSQLDBA7 SSChampion Points: 10709 More actions · Answer 2

Thanks SSCrazy. BTW, can this type of script be ran/scheduled using a Job Step within a SQL Server Job? For example, ActiveX Script, CmdExec, Transact-SQL, etc. How would I set it up, run and schedule it?

Thanks, Kevin

Perry Whittle SSC Guru Points: 233850 More actions · Answer 3

yes execute in a job step as ActiveX VB script, paste the code straight into the job step dialog

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

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

Perry Whittle (10/13/2011)
i use a simple VBScript to prune files
Option Explicit
'Delete all SQL Server backup files more than x days old
Dim oFS, oSQLBackupFol, oFol, oFil
Set oFS = CreateObject("Scripting.FileSystemObject")
Set oSQLBackupFol = oFS.GetFolder("D:\MSSQL\Backup") 'Change this as appropriate
For Each oFol IN oSQLBackupFol.SubFolders
For Each oFil in oFol.Files
If oFil.DateCreated < Now-8 AND Ucase(right(oFil.name, 3)) = "BAK" then
oFil.Delete
End If
Next
Next
set oFol = nothing
set oSQLBackupFol = nothing

Perry, from where do you invoke your good script from?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Agh! Apologies, Perry. I didn't scroll down far enough to see your previous answer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 233850 More actions · Answer 6

there's probably a way through Powershell to do this but i just havent had time to explore it yet 🙂

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

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