asked
Please provide a script which will delete old back
Please provide a script which will delete old bak file of 1 day. I can't use maintenance plan.
I have a solution using XP__CMDSHELL_, I know enabling XP_CMDSHELL is a security risk.... Sorry!.
-- Use this script inside your Job
DECLARE @SQL VARCHAR(800),
@ID INT,
@MaxID INT,
@BackupPath VARCHAR(500)
declare @temp TABLE
(ID INT IDENTITY,
backupPath VARCHAR(500)
)
INSERT INTO @temp
SELECT 'DEL '+physical_device_name
FROM msdb.dbo.backupmediafamily mf
INNER JOIN msdb.dbo.backupset bs
ON bs.media_set_id = mf.media_set_id
WHERE DATEDIFF(dd,backup_finish_date,GETDATE()) = 1
AND type = 'D' -- Database backup
SELECT @id = 1, @maxID = MAX(ID)
FROM @temp
WHILE @id
It can be done better using powershell script. Another option is
- Create a text file on any location.
- Create a SQL Server Job as follows
You need to create two Job steps both should be of type Operating System(CmdExec)
Step1: use the following command
SQLCMD -C -S ServerName -q "SELECT 'DEL '+physical_device_name FROM msdb.dbo.backupmediafamily mf INNER JOIN msdb.dbo.backupset bs ON bs.media_set_id = mf.media_set_id WHERE DATEDIFF(dd,backup_finish_date,GETDATE()) = 1 AND type = 'D'"
-o the-location-of-the-text-file-you-created-earlier
Step2: Click open and choose the file you created earlier
- Schedule it to run every day.
You could also try using VBScript - [@ThomasRushton has an example on his Blog][1] [1]:
http://thelonedba.wordpress.com/2010/10/18/sql-server-2005-rtm-maintenance-cleanup-fail/
Hi, You don't need to write a script to delete your backup file. you can set the maintenance plan to do it. There is called 'Maintenance CleanUp Task'.
Which doesn't work properly with SQL Server 2005 RTM... of course, we don't know which version of SQL Server @chana is using...
It's always fun to throw out a CMD version... forfiles /P /S /D -1 /C "cmd /c del /q @path"
i think this helps you very well Option Explicit 'Delete all SQL Server backup files more than 1 days old Dim oFS, oSQLBackupFol, oFol, oFil Set oFS = CreateObject("Scripting.FileSystemObject") Set oSQLBackupFol = oFS.GetFolder("D:\SQL Server Backups") ' Change this to the right path For Each oFol IN oSQLBackupFol.SubFolders For Each oFil in oFol.Files If oFil.DateCreated < Now-1 Then 'If you want to make sure it's a .bak or .trn file, check oFil.Name, otherwise 'This will delete anything more than a day old in the directory 'all first level subdirectories. (Recursion is left as an exercise for the reader) oFil.Delete End If Next Next
That looks familiar... Could it be the code I wrote in my blog post as noted by @WilliamD?
it is probably referenced from here
http://ask.sqlservercentral.com/questions/21049/want-to-delete-i-day-back-baktrn-file-using-script
Try using this procedure. It is an undocumented procedure from MS so the functionality could break during an upgrade or SP, but I find that risk easier to deal with than enabling xp_cmdshell or SSIS based maintenance plans. exec master.dbo.xp_delete_file 0, 'c:\temp', 'bak', '2010-10-26T17:00:00' [
http://sqlblog.com/blogs/andy_leonard/archive/2009/03/11/xp-delete-file.aspx][1] [1]:
http://sqlblog.com/blogs/andy_leonard/archive/2009/03/11/xp-delete-file.aspx
