SQL Server Dump with Powershell for backup

The first database that I did something useful was MySQL. I must admit I like that RDBMS but one thing that struck me, was how easy it is to dump whole database to a file.

So, I made script and put it in cron (a linux based Task Scheduler). The compression of file was by 7zip, not very planetary known, but awesome and best compression I’ve found.

The script backup.sh looks like this:

#!/bin/bash

FILE="dump-`date +%Y-%m-%d`.sql"

mysqldump -u root -psecretpass -c -e -K -r $FILE databasename

7z a -t7z $FILE.7z $FILE -m0=BCJ2 -m1=LZMA:d23 -m2=LZMA:d19 -m3=LZMA:d19 -mb0:1 -mb0s1:2 -mb0s2:3

rm $FILE

So, since last two years I’m working mostly in SQL Server. I tried to find the way to create this kind of script. I really don’t like SQL Server backups (.bak). I was a very astonished, for there’s no way for fully dump the SQL Server Database. The only way I found is through SQLDumper. A small software for dumping only INSERTs. Tables creation are not included, I wondered why, but this was my only option!

I wanted to use Powershell for scripting, and transition of upper bash code to Powershell was a quite a journey to me, it wasn’t easy but after few hours of searching, reading powershell documentation, I finally did it.

The script backup.ps1 looks like this:

$FILE="Backup_$(Get-Date -format "yyyy-MM-dd_HH-mm").sql"

.\SQLDumper.Console /filename=.\Backup.xml

ren .\backup.sql "$FILE"

.\7z a -t7z "$FILE.7z" "$FILE" -m0=BCJ2 -m1=LZMA:d23 -m2=LZMA:d19 -m3=LZMA:d19 -mb0:1 -mb0s1:2 -mb0s2:3

rm "$FILE"

echo Done...

A quick walkthrough of the script:

1. Create variable $FILE to hold the name of file with current date and time, and sql file extension
2. With SQLDumper console application create dump of database INERTSs (a backup.sql file), through the SQLDumper XML Templates (Backup.xml)
3. Renaming backup.sql to meaningful name (from $FILE variable, with Date and Time of backup)
4. Compressing the file with 7zip
5. Remove the .sql file
6. Done…

SQLDumper

SQLDumper has a GUI and console application. To easily crate the SQLDumper XML template, for first time is needed to run GUI version. Though it can be done manually by writing XML.
Steps are very easy in GUI environment:

1. Connect to Database
2. choose tables/fields
3. choose the directory to put the dump file (in my case D:\backup)
4. file name for backup file (in my case backup.sql)
5. save this template by File->Save Template.

Note: be sure to use the 3+ version of SQLDumper, because the earlier release of SQLDumper console was very hard to use as script. I couldn’t run the command as scheduled script, because in some strange way SQLDumper had “Press any key to continue…” at the end of the script, and script was hanging there, waiting for key, and I didn’t know how to simulate key with powershell, so I used 3+ version, in which this strange behavior was removed.

Task Scheduler

Task Scheduler is very easy to use, so I’ll not going through it step by step. The only step I’ll go is that Task Scheduler need an Action, and for action is used “Start a program”. Since the powershell script can’t be referenced, there is need for usual batch (.bat) script.

The backup.bat script looks like this:

powershell -command "& 'D:\backup\backup.ps1' "

And also be sure that your scripts are Remotely signed to run. Beware this is a security risk, you should signed it with Certificate!

To remotely sign the powershell scripts use this command:

set-executionpolicy RemoteSigned

Hope it helps, if you have quetsions/modifications about the post, please do write!