SQLBkup 32 - ERASEFILES does not delete long file names

Brian Donahue

SQL Backup can optionally erase files that exceed a certain age, using the erasefiles option. There is a known issue with the software in that the filename buffer is limited to 22 characters. This means that any filenames over 22 characters in length will not be erased.

This will be addressed in a future release, but for now, the suggested workaround if to use the script below. This is a JavaScript running under Windows Scripting Host, so you may use this from a cmdExec task in a SQL Agent job, or you can include a call to xp_cmdshell to run it. Bear in mind that by default, only system administrators can run a cmdexec task or use xp_cmdshell.

To install and use this workaround, copy and paste the code below into Notepad and save it with a .js extension. You can then run it using cscript.exe, specifying the days and root folder.

For example:
cscript deleteoldbackups.js /days:30 /rootfolder:"c:\backups"

/* this accepts two arguments, the number of days old a file must be to get deleted 
and the root folder to start deleting from */

if (!WScript.Arguments.Named.Exists("days") && !WScript.Arguments.Named.Exists("rootfolder")) {
	WScript.Echo("must specify date criteria (/days:x)\r\nand root directory (/rootfolder:z)");
	WScript.Quit();
}
var deleteage=WScript.Arguments.Named("days");
var rootfolder=WScript.Arguments.Named("rootfolder");

/* We will need a FileSystemObject */
var FS=new ActiveXObject("Scripting.FileSystemObject");

deleterecursive(rootfolder,deleteage)


function deleterecursive(folder, age) {
DeleteOldFiles(folder,age);
var obFolder=FS.GetFolder(folder);
var subfolders=obFolder.SubFolders;
var enSubFolders=new Enumerator(subfolders);
	for (;!enSubFolders.atEnd(); enSubFolders.moveNext()) {
		var newfoldername=enSubFolders.item().Path
		//Un-comment next line to see folder names
		//WScript.Echo(newfoldername)
		deleterecursive(newfoldername, age)
	}

}

function DeleteOldFiles(folderName, daysOld) {
var obFolder=FS.GetFolder(folderName);
var f=new Enumerator(obFolder.Files)
var d = new Date();

var oldDate=new Date(d.getYear(), d.getMonth(), d.getDate()-daysOld);

	for (;!f.atEnd();f.moveNext()) {
		var file=f.item();
		//WScript.Echo(f.item().Name)
		if (file.DateLastModified < oldDate)
			file.Delete();
	}
	
}

Brian Donahue

Database names can be up to 127 characters in length in SQL Backup 4.x. Erasefiles also works with filenames of this length.