This is the process:
- backup SQL server DB.
- compress backup file with 7zip. (7zip website)
- rename the file and copy it to a backup location.
- send an email notification that the backup completed using blat.
It sounds simple right? Well it can be a bit difficult to tie it all together so here are some scripts that I have used to achieve this.
- Create backup device
- Open SQL server management studio
- Expand Server Objects
- Right Click Backup Devices
- Click new backup device
- Give the device a name (you need this for the batch script so try excluding spaces) and give it a backup path. I prefer using another disk and a simple path, something like D:\backups\db-backup.bak
- The backup batch script should look something like below, just substitute the databasename to the name of the database you are backing up and backup-location to the name of the backup device you specified above.
sqlcmd -S localhost\databasename -E -Q "BACKUP DATABASE [databasename] TO [backup-location]"
IF NOT EXIST "D:\backup\db-backup.bak"
set subject=Backup error : SQL Backup failed
set body=SQL Backup failed. %date% %time%
- Again update the script below accordingly. Remember the 7zip directory needs to have no spaces otherwise the script does not work.
%zipdir%\7z.exe a "D:\backup\db-backup.bak" "D:\backup\db-backup-%date:~0,4%%date:~5,2%%date:~8,2%.7z"
IF NOT EXIST "D:\backup\db-backup-%date:~0,4%%date:~5,2%%date:~8,2%.7z"
set subject=Backup error : 7zip failed
set body=7zip operation failed. %date% %time%
- This is pretty simple again just update the script as per you needs
xcopy "D:\backup\db-backup-%date:~0,4%%date:~5,2%%date:~8,2%.7z" "\\backupserver\folder" /s /d
IF EXIST "\\backupserver\folder\db-backup-%date:~0,4%%date:~5,2%%date:~8,2%.7z"
set subject=Backup successful
set body=Backup successful. %date% %time%
- This function requires BLAT, just download blat and copy it to a directory something like C:\blat. It s a great command line app that I have used before for sending mails from some PHP scripts.
REM add addresses separated by commas
set appname=SQL Backup App
%blatlocation%\blat.exe -to %toaddress% -i "%appname%" -server %smtpserver% -f %fromaddress% -subject "%appname% : %subject%" -body "%body%"