Backup MSSQL Databases with BackupPC

I did this before with Bareos/Bacula. I've since decided that product is a bit too black box for my liking. It always did what I asked of it, but the database would crash here and there and that freaked me out a bit. On to something new, and BackupPC was surprising better than I thought it would be. Admittedly it doesn't have an agent in the traditional sense which I don't like, but I was still able to achieve the needed goals.

• Working BackupPC installation
• SQL server account with sa-like privs - we'll create a more restricted backup user
• Unprivileged account on domain or local to server for backuppc user
• You have to have done this or something like it

1. Open SQL Server Management Studio - connect to the DB Engine in question as a SA or some such
2. Drill into your Database engine's Security folder, right-click Logins and click New Login…
3. In the Login - New dialogue fill in the Login name field with your Windows user account, use the Search… dialogue to make things simple. This can be a local or domain user. That's the only field that needs to be filled in, click OK.
4. In the Databases folder, expand each Database you want to back up (often there is only 1), expand the Security folder within, right-click Users and click New User…
5. Fill in User name and Login name with the information from step 3, in the Membership section check db_backupoperator, click OK
6. Repeat steps 4 & 5 for all databases if you want to backup more than one
7. Now is a good time to make a specific note of all the database names you want to back up, the database name is the actual text adjacent to the cylindrical shape, this will be used in the next section

1. Create a folder somewhere on the server, I use a folder called “dbbackups” in the root of the C drive, it doesn't matter what it's called or where it is as long as there is enough free space to save the entirety of your SQL dumps there
2. Change the permissions to be very restrictive on that folder, only the unprivileged windows user and the user that is running the SQL service (find in services.msc) need access.
3. We need 2 files in that folder
   

   backup_dbs.cmd

   erase "c:\dbbackups\*.bak"
   sqlcmd -E -i "c:\dbbackups\backupdbs.sql"

   If you aren't using the default instance of SQL Server on your Windows host you may need to add something like

   -S .\MYINSTANCE

   to the sqlcmd line. Incidentally you may have more than one of the following .sql files and more than one sqlcmd to run if you're needing to backup multiple running SQL instances on one host.

   backupdbs.sql

   BACKUP DATABASE [DATABASE_1] TO  DISK = N'c:\dbbackups\DATABASE_1.bak' WITH NOFORMAT, NOINIT,  NAME = N'DATABASE_1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
   BACKUP DATABASE [DATABASE_2] TO  DISK = N'c:\dbbackups\DATABASE_2.bak' WITH NOFORMAT, NOINIT,  NAME = N'DATABASE_2-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
   GO

1. The user on your BackupPC server that is running the daemon needs to be able to login to the Windows host without a password, you can ID this user with `ps -ef | grep “BackupPC -d”`, in my case it's “backuppc”
2. If you haven't already:
   1. su backuppc
   2. ssh-keygen
   3. cat .ssh/id_rsa.pub (copy this to your clipboard)
   4. exit
3. copy the contents of the backuppc user's id_rsa.pub file into the file “.ssh/authorized_keys” on the Windows host AS that user:
   1. su backuppc
   2. ssh windowsuser@windowshost (yes, then password for that user)
   3. mkdir .ssh
   4. vi .ssh/authorized_keys (paste the contents from the id_rsa.pub file here)
   5. chmod 600 .ssh/authorized_keys
   6. chmod 700 .ssh
   7. exit
4. At this point you should be able to ssh into the windows host as backuppc user without entering a password
5. Create a host just like any other Windows host on your BackupPC server, you may as well configure this server to only do Full backups since really every backup will be full anyway using this method
6. Configure your DumpPreUserCmd to be the following: “$sshPath -q -x -l backuppc $host /cygdrive/c/dbbackups/backup_dbs.cmd”

That should be it, you should get backups. Just that easy - if it doesn't work, read the logs.