How to backup MSSQL express databases

OK, so I had to backup an MSSQL express database the other day. In any other scenario, there's no problem: all you have to do is create a job for it. But MSSQL express? Hmmm...

Well, this is the route I'm going to go for: Create a Samba Share and use expressmaint for the actual backup.

Since creating a Samba share is out of the scope of this document, I'll just run through the basics:

Install Samba and create the share:

[root@dbbak ~]# yum -y install samba samba-client samba-common samba-doc
[root@dbbak ~]# cp /etc/samba/smb.conf /etc/samba/smb.conf.bak
[root@dbbak ~]# vi /etc/samba/smb.conf
....
#============================ Share Definitions ==============================

[backups]
        comment = Database Backups
        path = /backups
        valid users = dbbak
        public = no
        browseable = yes
        guest ok = no
        writable = yes
        printable = no
        create mask = 0765

Create the user:

[root@dbbak ~]# useradd dbbak -g users -m -s /bin/bash
[root@dbbak ~]# smbpasswd -a dbbak
[root@dbbak ~]# mkdir /backups
[root@dbbak ~]# chown -R dbbak:users /backups/
[root@dbbak ~]# passwd dbbak
[root@dbbak ~]# smbpasswd -a dbbak

Let's assume that I'll be using 12345678 as my password.

The ports that we'll need to add in iptables for SMB to work are:

-A INPUT -m state --state NEW -m udp -p udp --dport 137 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 137 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 138 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 138 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 139 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 139 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 445 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 445 -j ACCEPT

And finally:

[root@dbbak ~]# service smb restart
[root@dbbak ~]# chkconfig smb on

OK, now that we have our storage let's move on to the actual MSSQL backup.

First of all, we'll need to fire up the SQL Configuration Manager and make sure TCP/IP and Named Pipes are enabled:

And then, go to "SQL Server Network Configuration", expand "Protocols for ..." and double-click on "TCP-IP" (or right-click on "TCP-IP" and select "Properties").

Make sure that the "Dynamic Ports" field is empty everywhere and the "TCP Port" field is 1433.

Now, after restarting the MSSQL service, you need to go ahead and download expressmaint from http://expressmaint.codeplex.com and put it in a directory of your choice.

Create a batch job for it:

@ECHO OFF
net use Z: \\192.168.1.48\backups /USER:dbbak /PERSISTENT:NO 12345678
mkdir "C:\MMSQL_BK-%DATE:~4,2%%DATE:~7,2%%DATE:~10,4%"
"C:\Users\administrator\Downloads\expressmaint" -S (local) -D ALL -T DB -B "C:\MMSQL_BK-%DATE:~4,2%%DATE:~7,2%%DATE:~10,4%" -BU DAYS -BV 1 -R "C:\MMSQL_BK-%DATE:~4,2%%DATE:~7,2%%DATE:~10,4%" -RU DAYS -RV 1 -V -C
xcopy /E /Y /Q "C:\MMSQL_BK-%DATE:~4,2%%DATE:~7,2%%DATE:~10,4%" "Z:\MMSQL_BK-%DATE:~4,2%%DATE:~7,2%%DATE:~10,4%\"
net use Z: /DELETE
rmdir /S /Q "C:\MMSQL_BK-%DATE:~4,2%%DATE:~7,2%%DATE:~10,4%"
exit

This script assumes that my Samba share's IP is 192.168.1.48, the user is dbbak and the password 12345678. You should also check expressmaint's options to suit your needs.

Finally, all that's left is to create a scheduled task for it.