The idea of this post come in my mind when last week my SQL server on 2008r2 is crashed and bak files also in same servers, the windows backup is not perform last night and due to holiday of two days we get notification of this issue three days later.
So i decides we can create an script which will copy bak (even you can change for any other types of file extension for backup) on other storage server and delete files older than 7 days and also update the same via Email, what files has copied and if not the email alert will trigger something is wrong.
The Application we require for script.
- mailsend (opensource application more info can found on “http://code.google.com/p/mailsend/“)
- xcopy (Inbuilt application in windows)
- Task Scheduler (Inbuilt application in windows)
- SMTP server address (from where we can send mail using this server in our case we have SMTP Server on 192.168.0.15 or we can use free SMTP Servers from Internet List)
- Source and destination path with username and password.
- Source D:SQLBACKUP (where all bak files created)
- Destination \192.168.0.5Backup
- User name for backup access is “backup” password “Backup@14”
This log file will be used for backup and diagnose status and errors if any. for to execute this batch script we need to delete previously created log file.
Open Notepad add the net use command with destination folder path in persistent
net use /PRESISTENT:YES \192.168.0.5Backup /u:backup Backup@14 > sqlbackup.log
The following command make session with the destination server & copy the output in sqlbackup.log file which will bu usable for following command.
more info can found on “http://en.wikipedia.org/wiki/XCOPY”
move is command which will move all .bak files in copied folder so next time xcopy only copy new files there are date options also available in xcopy which i am not covering in this Tut as this is an simple script for backup.
The mailsend.exe is the opensource application which we have downloaded from http://code.google.com/p/mailsend/ as the following command switches is use as
- -smtp the smtp address in our case is 192.168.0.15
- -from the mail will send from we use SQL as name but it can also the particular server address that will be better.
- -to where we want to send mail every corp. has there own helpdesk Email ID.
- -sub the proper subject should be mention like the backup is done.
- -attach the log file will attached in email as well as the text/plain,i command will add the logfile text in email body.
Those code are same only -sub is changed to show the backup is Failed.
Now the finishing touch for our script.
net use \192.168.0.5Backup /del
// 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);
var c = strPath.substr(-1, 1);
if( c !== ‘\’ && c !== ‘/’ )
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)
Save the file a “DeletesOlderThan.js” save same folder where our batch file is saved and as the default this script will delete files older than 30 days that can be overwritten adding following code in our script.
wscript “c:path to fileDeletesOlderThan.js” 7
The D: and cd SQLBACKUPCopied will change dir the bak files is stored, then wscript 7 will only leave the past 7days file in folder.
add the file in Task Scheduler with suitable time and DONE.
See how simply we can schedule our daily work using scripts.