-- 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] : http://thelonedba.wordpress.com/2010/10/18/sql-server-2005-rtm-maintenance-cleanup-fail/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 NextTry 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] : http://sqlblog.com/blogs/andy_leonard/archive/2009/03/11/xp-delete-file.aspx
Write an Answer
No one has followed this question yet.