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.

Windows System error 1219, multiple connections to a server or shared resource by the same user

Here's a funny one I ran into earlier.

So I try to mount a SAMBA share on a Windows system. I get this error:



Wait, are you telling me that I am restricted to having only one share subdirectory connected per PC? What happens if I need two? Now that's some crappy engineering right there Batman!

Oh, I know what this is. It's probably one of those Windows "features" and if I do the same thing from the command line, it'll just work, right? Right? Wrong.

F:\Documents and Settings\user>net use Q: \\192.168.0.254\DIR2 /USER:shareuser /PERSISTENT:NO
The password is invalid for \\192.168.0.254\DIR2.

Enter the password for 'shareuser' to connect to '192.168.0.254':
Multiple connections to a server or shared resource by the same user, using more than one user name, are not allowed. Disconnect all previous connections to the server or shared resource and try again..

Through some digging I managed to find out that this is by design. Windows will connect to a share once; if you try to connect again, it will block you. If you want to connect to a different subdirectory of your SAMBA server, you will need to disconnect from the one you are currently connected to.

Fret not though, for this is Windows; home of the crappiest, nastiest pieces of engineering ever came to existence.

Workaround? Just edit your %WINDIR%/system32/drivers/etc/hosts file and add some more entries that correspond to your share's IP address. For instance:

192.168.0.254 foo
192.168.0.254 bar
192.168.0.254 foobar
192.168.0.254 fubar

And now I can connect to my share using my new aliases:

F:\Documents and Settings\user>net use Q: \\fubar\DIR2 /USER:shareuser /PERSISTENT:NO
The password is invalid for \\fubar\DIR2.

Enter the password for 'shareuser' to connect to 'fubar':
The command completed successfully.

Oh Windows how I hate thee with all my passion.