Differences

This shows you the differences between two versions of the page.

--- wiki:backuppc_mssql [2015/06/03 21:10] – jrdalrymple
+++ wiki:backuppc_mssql [2018/09/27 02:29] (current) – external edit 127.0.0.1
@@ Line 24: / Line 24: @@
 ==== 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>If you aren't using the default instance of SQL Server on your Windows host you may need to add something like<code>-S .\MYINSTANCE</code>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.<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.