14 October 2015

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 "
      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"

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

      %blatlocation%\blat.exe -to %toaddress% -i "%appname%" -server %smtpserver% -f %fromaddress% -subject "%appname% : %subject%" -body "%body%"
      GOTO END

No comments: