Bitnami DokuWiki

You've loaded an old revision of the document! If you save it, you will create a new version with this data.

====== Backup MSSQL Databases with BackupPC ======

==== Introduction ====

I [[https://www.crummylogic.com/wordpress/?p=93|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.

==== What you'll need ====

* 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 [[wiki:cygwin_install_backuppc_rsyncd|done this]] or something like it

==== The SQL Permissions ====

- Open SQL Server Management Studio - connect to the DB Engine in question as a SA or some such
  - Drill into your Database engine's Security folder, right-click Logins and click New Login...
  - 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.
  - 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...
  - Fill in User name and Login name with the information from step 3, in the Membership section check db_backupoperator, click OK
  - Repeat steps 4 & 5 for all databases if you want to backup more than one
  - 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

==== The folder and scripts ====

- 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
  - 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.
  - We need 2 files in that folder\\ <file DOS backup_dbs.cmd>
erase "c:\dbbackups\*.bak"
sqlcmd -E -i "c:\dbbackups\backupdbs.sql"</file><file DOS 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</file>

==== The parts on the BackupPC server ====

- 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"
  - If you haven't already:
      - su backuppc
      - ssh-keygen
      - cat .ssh/id_rsa.pub (copy this to your clipboard)
      - exit
  - 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:
      - su backuppc
      - ssh windowsuser@windowshost (yes, then password for that user)
      - mkdir .ssh
      - vi .ssh/authorized_keys (paste the contents from the id_rsa.pub file here)
      - chmod 600 .ssh/authorized_keys
      - chmod 700 .ssh
      - exit
  - At this point you should be able to ssh into the windows host as backuppc user without entering a password
  - 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
  - Configure your DumpPreUserCmd to be the following: "$sshPath -q -x -l backuppc $host /cygdrive/c/dbbackups/backup_dbs.cmd"

==== Conclusion ====

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

Edit summary

Note: By editing this page you agree to license your content under the following license: CC Attribution-Share Alike 4.0 International