MSSQL backup Script retaining 3 days backup

I was looking for a script, which takes backup of MS SQL databases and store it some location. The retention period should be 3 days. I googled and found script. Run below steps if you have such requirement.

First we will create a script which will search files older than 3 days and delete them. So copy/paste below script content into "DeletesOlderThan.js" file.

Please note: this file will delete content of the current directory, so before using this file need to go to particular folder which data needs to be deleted.

To invoke this file, run following command. (Here 3 is the #day. This command will delete files older than 3 days)

wscript "e:\DeletesOlderThan.js" 3

////////////////////////////////////////////////////////
// Deletes file older than a number of days
// in the current directory
////////////////////////////////////////////////////////
// Usage: wscript DeleteOlderThan.js [#Days]
// By default, remove files older than 30 days
////////////////////////////////////////////////////////

function removeDays(date, nDays)
{
var dateRet = date
return dateRet.setDate(date.getDate() - nDays);
}

function addSlash(strPath)
{
var c = strPath.substr(-1, 1);
if( c !== '\\' && c !== '/' )
{
strPath += '\\';
}
return strPath;
}

// Read arguments
var nDays = WScript.Arguments(0) || 30;

// Create system objects
var fs = WScript.CreateObject("Scripting.FileSystemObject");
var shell = WScript.CreateObject("WScript.Shell");

// Retrieve current directory
var strDirectoryPath = addSlash(shell.CurrentDirectory);

// Compute date
var dateNow = new Date();
var dateTest = removeDays(dateNow, nDays);

// Iterate on files
var folder = fs.GetFolder(strDirectoryPath);
var files = folder.Files;

for( var it = new Enumerator(files); !it.atEnd(); it.moveNext() )
{
var file = it.item();

if( file.DateLastModified < dateTest)
{
file.Delete(true);
}
}

For MS SQL db backup, I am just taking its data and log file backup. So below command will be enough I guess. (In below command I am taking backup of "mydb" database. I have installed 7zip to compress backup).

 

"C:\Program Files\7-Zip\7z.exe" a -tzip e:\Backup\mydb-%V_Day%-%V_Month%-%V_Year%.zip "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\mydb.mdf" "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\mydb.ldf"

I hope this will be helpful.