Schedule Backup with Email Alert

As system admin we always need to perform backup and recovery work on server. an we aware that new windows server 2008 and above don’t provide tape backup services any more and windows backup application is always create issue with multiple domain environment where those domain and not have any type of sync relationship with each other.

 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.

1. mailsend (opensource application more info can found on “http://code.google.com/p/mailsend/“)
2. xcopy (Inbuilt application in windows)
3. Task Scheduler (Inbuilt application in windows)
4. 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)
5. Source and destination path with username  and password.
1. Source D:SQLBACKUP (where all bak files created)
2. Destination \192.168.0.5Backup
3. User name for backup access is “backup” password “Backup@14”

Let’s Start

Code:
del sqlbackup.log

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

Code:
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.
Code: 
net use \192.168.0.5Backup /del

Simple code to disconnect the created session all is done but wait as we aware the back files which created from sql its still in copied folder and we olny cant past 7 days files should remain on sql server so the javascript in batch file will help us.

Code:
////////////////////////////////////////////////////////
// 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);
    }

}

Here
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.

Code:
D:
cd SQLBACKUPCopied
wscript “c:path to fileDeletesOlderThan.js” 7
exit

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.