Backup and Compress SQL Server Express Backup

I have multiple MS SQL express servers that I need to manage and backup and I have found that the backups can get quite large, so I looked into methods of compressing the backups. I found that there is a builtin method in SQL server, but this is only for a licensed copy of SQL server, not for the express edition. So being the hacker that I am I decided to do it myself with some scripting and 7zip.

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.

1. 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
  
  
2. Backup Database Script
• 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%
      GOTO SENDMSG
3. Compress the backup file using 7zip and rename file
• Again update the script below accordingly. Remember the 7zip directory needs to have no spaces otherwise the script does not work.
  
  set zipdir=C:\progra~1\7-Zip
  %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% 
      GOTO SENDMSG
4. Copy the file to a remote location
• 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%
      GOTO SENDMSG
5. Send email message
• 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.
  
  set blatlocation=C:\blat
  set smtpserver=mail.myserver.com
  REM add addresses separated by commas
  set toaddress=address@myserver.com
  set fromaddress=address@myserver.com
  set appname=SQL Backup App
  
  :SENDMSG
  %blatlocation%\blat.exe -to %toaddress% -i "%appname%" -server %smtpserver% -f %fromaddress% -subject "%appname% : %subject%" -body "%body%"
  GOTO END