I have tested Duplicati in both scenarios and I am not aware why I should choose to backup MSSQL databases by actually ticking them in the databases section rather than selecting the actual files in their respective locations.
In my tests both scenarios backup the data without error (unless you tick the installation folder which is a known issue). But from my tests I see that incremental backups of the job where I an selecting the databases are way slower then the job where I selected the actual database files. In both scenarios I am using snapshot policy required. Can anyone shed some light as to which way is the recommended way and what are the risks in doing it one way over the other?
When you select the MS SQL databases, Duplicati will talk to the SQL engine to facilitate the backups. This in general is a better approach as the database will be in an application-consistent state. Also, the SQL engine will know that a backup has been performed and may do other things like truncate transaction logs.
If instead you are backing up the raw files, Duplicati reads them like any other file on the filesystem. They are not guaranteed to be in an application-consistent state.
This is my understanding based on experience with enterprise grade backup products. I do not have direct experience with native MS SQL backups in Duplicati, so please test thoroughly. Nothing requires you to select only one method - you could do both if you want.
Thank you for the reply. Can you please elaborate on what application-consistent state actually means? Using other backup tools I would just create a volume shadow copy and backup the raw mdf/ldf pairs and this worked pretty much every time.
I am also very interested in a developer insight on what the SQL engine actually does to facilitate the backups as the results are basically the same set of files (mdf and ldf pairs) and not .bak files that you would get if you ran the backup database statements from within SQL.
I would be interested in this as well… We are looking at this for many of our remote machines and I would very VERY much like to see something like creation of .bak files. Whatever I could do to help implement this I’m happy to do.
Currently I’ve been beating my head trying to get a PS script to pre-run. That has been a bit of a pain.
I don’t want to presume but I’m curious if Duplicati is talking to the SQL engine how hard would it be to add an option to get it to create a .bak file?
You could probably trigger a SQL backup maintenance plan in your Duplicati pre-backup script, then back up the BAK files only. I’m not really a SQL guy but I don’t know why that would be better than backing up the databases via the engine.
That is what I was working on. However, there are limitations that will not allow a ps1 script to fire directly. I could use another language but I would rather not.
Extremely interesting point. Indeed it seems to make sure that the SQL VSS is enabled. Maybe making sure that SQL VSS is enabled the software makes sure, as @drwtsn32 suspected, that the database is in an application-consistent state.
I would really love to know how it does that though, too …
This looks like it’s hard to avoid, provided there’s a VSS writer. One thing MS SQL code does is check.
The backup program VSS requestor looks like it just has to tell VSS to prepare, then get volumes here.
I work with 3 or 4 vendors and all request that I not use VSS writer for back. They insist that I run Full,Diff and Log backups to a backup device. I would suggest that this would be the best way to proceed.
You could get the SQL Backup to trigger the Duplicati backup using an event viewer task which would call Duplicati-client script to start the required backup task.
For me that is over kill I just schedule the Duplicati back to start at a time after the sql finihes.
The longest Full DB Backup I have is 2 hours for a 20GB Database
You talking about using a database maintenance plan to create database backups/dumps (as BAK files)? That’s usually the “safest” option and one vendors have no problem with supporting. That being said, many enterprise-grade backup solutions that exist for backing up SQL natively without doing the dump step and they work 100% as well.
I don’t consider Duplicati enterprise-grade and don’t use its native SQL backup, so I can’t really vouch for it. But vendors “not supporting” anything besides the most basic way to do something is pretty typical in the industry.
In any case, if it were me I’d probably go that route - schedule the database dumps in a maintenance plan and then schedule Duplicati to back up those dumps. Before I’d use the native SQL backups I’d do a lot of testing first, and I haven’t done that with Duplicati.
What I wouldn’t do is back up the raw MDF/LDF files with Duplicati as they may not be in an application-consistent state (if SQL is running at the time), and by itself that provides no mechanism for SQL to truncate logs AFAIK.
I have been using the option to backup SQL files by using the SQL VSS writer in order to get application-consistent backups and seem to have good results so far. I am using scripts to attach and check the databases offsite and the results are promising so far. I am getting higher backup times but the alternative (inconsistent backups) is not acceptable.
I am thinking of trying using a script to dump back files to a folder and backup that but I believe the time toll will be too high and if the other method works ok than there is no need.
Anyway I will keep you posted.
I will select @drwtsn32’s response as the Solution as I understood the difference between the methods from his answer although @ts678’s insight was most helpful too .
