David Bird (1/5/2011)
I could see using this script on a SQL express installation without SQL Agent installed. Windows Scheduled Taks would be used for scheduling it.I am not sure how I would use a SQL script to delete the old backup without using XP_CMDSHELL. So if I need to use DOS commands to delete the old backup why not use Powershell for file deletes and backups.
Thanks Jimmy for the article
If you're interested in using SQL Server to delete your old backup files without using xp_cmdshell, you could look into master.dbo.xp_delete_file
exec master.dbo.xp_delete_file
0, --File type [(0) FileBackup | (1) FileReport]
N'<folderPath>', -- Folder path to look in
N'<ext>', --File extension to be deleted (do not precede with a period)
N'<date>', --Date line. All files meeting criteria created before this date will be deleted
1 -- Subfolder flag. Indicates to delete from subfolders [ 1 | 0 ]
keep in mind this procedure is undocumented and not supported, use at your own risk.
I use it and it works fine for deleting native sql backups. This procedure is what is run when a maintenance task for deleting backup files is scripted. Please do plenty of testing before implementation.