# Yet another MS SQL related topic - SQL Databases vs. file backup **URL:** \ **Category:** How-To\ **Created:** [August 18, 2020, 2:50pm UTC](https://forum.duplicati.com/t/yet-another-ms-sql-related-topic-sql-databases-vs-file-backup/10529 "2020-08-18T14:50:43Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![bogdantomasciuc](https://forum.duplicati.com/user_avatar/forum.duplicati.com/bogdantomasciuc/32/4490_2.png) [@bogdantomasciuc](https://forum.duplicati.com/u/bogdantomasciuc)\ **Post date:** [August 18, 2020, 2:50pm UTC](https://forum.duplicati.com/t/yet-another-ms-sql-related-topic-sql-databases-vs-file-backup/10529/1 "2020-08-18T14:50:43Z")
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? Thank you! ---
**Author:** ![drwtsn32](https://forum.duplicati.com/user_avatar/forum.duplicati.com/drwtsn32/32/2591_2.png) [@drwtsn32](https://forum.duplicati.com/u/drwtsn32)\ **Post date:** [August 21, 2020, 2:47pm UTC](https://forum.duplicati.com/t/yet-another-ms-sql-related-topic-sql-databases-vs-file-backup/10529/2 "2020-08-21T14:47:21Z")
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. ---
**Author:** ![bogdantomasciuc](https://forum.duplicati.com/user_avatar/forum.duplicati.com/bogdantomasciuc/32/4490_2.png) [@bogdantomasciuc](https://forum.duplicati.com/u/bogdantomasciuc)\ **Post date:** [August 24, 2020, 9:31am UTC](https://forum.duplicati.com/t/yet-another-ms-sql-related-topic-sql-databases-vs-file-backup/10529/3 "2020-08-24T09:31:46Z")
> [@drwtsn32](#): > > 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. 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. ---
**Author:** ![Jonathan\_Woodard](https://forum.duplicati.com/user_avatar/forum.duplicati.com/jonathan_woodard/32/4776_2.png) [@Jonathan\_Woodard](https://forum.duplicati.com/u/Jonathan_Woodard)\ **Post date:** [August 24, 2020, 1:51pm UTC](https://forum.duplicati.com/t/yet-another-ms-sql-related-topic-sql-databases-vs-file-backup/10529/4 "2020-08-24T13:51:32Z")
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? Thank you for any insight! 🙂 ---
**Author:** ![drwtsn32](https://forum.duplicati.com/user_avatar/forum.duplicati.com/drwtsn32/32/2591_2.png) [@drwtsn32](https://forum.duplicati.com/u/drwtsn32)\ **Post date:** [August 24, 2020, 1:59pm UTC](https://forum.duplicati.com/t/yet-another-ms-sql-related-topic-sql-databases-vs-file-backup/10529/5 "2020-08-24T13:59:20Z")
Here’s an article that explains application vs crash consistent backups: > **[Application-Consistent & Crash-Consistent Backup - Differences](https://www.nakivo.com/blog/crash-consistent-vs-application-consistent-backup/)** > > There are definite differences in how crash-consistent and application-consistent backups handle application data such as MS SQL Server or MS Exchange. ---
**Author:** ![drwtsn32](https://forum.duplicati.com/user_avatar/forum.duplicati.com/drwtsn32/32/2591_2.png) [@drwtsn32](https://forum.duplicati.com/u/drwtsn32)\ **Post date:** [August 24, 2020, 2:02pm UTC](https://forum.duplicati.com/t/yet-another-ms-sql-related-topic-sql-databases-vs-file-backup/10529/6 "2020-08-24T14:02:34Z")
> [@Jonathan\_Woodard](#): > > 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. ---
**Author:** ![Jonathan\_Woodard](https://forum.duplicati.com/user_avatar/forum.duplicati.com/jonathan_woodard/32/4776_2.png) [@Jonathan\_Woodard](https://forum.duplicati.com/u/Jonathan_Woodard)\ **Post date:** [August 24, 2020, 2:22pm UTC](https://forum.duplicati.com/t/yet-another-ms-sql-related-topic-sql-databases-vs-file-backup/10529/7 "2020-08-24T14:22:58Z")
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. ---
**Author:** ![ts678](https://forum.duplicati.com/letter_avatar_proxy/v4/letter/t/8491ac/32.png) [@ts678](https://forum.duplicati.com/u/ts678)\ **Post date:** [August 24, 2020, 2:36pm UTC](https://forum.duplicati.com/t/yet-another-ms-sql-related-topic-sql-databases-vs-file-backup/10529/8 "2020-08-24T14:36:53Z")
> [@Jonathan\_Woodard](#): > > if Duplicati is talking to the SQL engine > [@Jonathan\_Woodard](#): > > create a .bak file I think it’s only talking in [VSS way](https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-vss-writer-backup-guide?view=sql-server-ver15). I don’t use SQL Server, but isn’t `.bak` creation done by [SQL](https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15) or tool? [https://github.com/duplicati/duplicati/blob/master/Duplicati/Library/Snapshots/MSSQLUtility.cs](https://github.com/duplicati/duplicati/blob/master/Duplicati/Library/Snapshots/MSSQLUtility.cs) is special MS SQL code which looks like it makes sure to [enable](https://github.com/duplicati/duplicati/blob/bcaedebecd1f47b22c4fdec25805667165dd4ad7/Duplicati/Library/Snapshots/MSSQLUtility.cs#L145) the SQL Server VSS writer, but why? The regular [snapshot-policy](https://duplicati.readthedocs.io/en/latest/06-advanced-options/#snapshot-policy) VSS enabler looks like it [just goes with](https://github.com/duplicati/duplicati/blob/bcaedebecd1f47b22c4fdec25805667165dd4ad7/Duplicati/Library/Snapshots/WindowsSnapshot.cs#L72) whatever writers are now enabled. If anyone has SQL Server, and knows VSS (and even better can read code) can add to this, feel free… ---
**Author:** ![bogdantomasciuc](https://forum.duplicati.com/user_avatar/forum.duplicati.com/bogdantomasciuc/32/4490_2.png) [@bogdantomasciuc](https://forum.duplicati.com/u/bogdantomasciuc)\ **Post date:** [August 24, 2020, 2:47pm UTC](https://forum.duplicati.com/t/yet-another-ms-sql-related-topic-sql-databases-vs-file-backup/10529/9 "2020-08-24T14:47:21Z")
> [@ts678](#): > > [duplicati/Duplicati/Library/Snapshots/MSSQLUtility.cs at master · duplicati/duplicati · GitHub](https://github.com/duplicati/duplicati/blob/master/Duplicati/Library/Snapshots/MSSQLUtility.cs) > is special MS SQL code which looks like it makes sure to [enable](https://github.com/duplicati/duplicati/blob/bcaedebecd1f47b22c4fdec25805667165dd4ad7/Duplicati/Library/Snapshots/MSSQLUtility.cs#L145) the SQL Server VSS writer, but why? 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 … ---
**Author:** ![ts678](https://forum.duplicati.com/letter_avatar_proxy/v4/letter/t/8491ac/32.png) [@ts678](https://forum.duplicati.com/u/ts678)\ **Post date:** [August 24, 2020, 3:25pm UTC](https://forum.duplicati.com/t/yet-another-ms-sql-related-topic-sql-databases-vs-file-backup/10529/10 "2020-08-24T15:25:08Z")
> [@bogdantomasciuc](#): > > the database is in an application-consistent state. This looks like it’s hard to avoid, provided there’s a VSS writer. One thing MS SQL code does is [check](https://github.com/duplicati/duplicati/blob/bcaedebecd1f47b22c4fdec25805667165dd4ad7/Duplicati/Library/Snapshots/MSSQLUtility.cs#L149). The backup program VSS requestor looks like it just has to tell VSS to prepare, then get volumes [here](https://github.com/duplicati/duplicati/blob/bcaedebecd1f47b22c4fdec25805667165dd4ad7/Duplicati/Library/Common/IO/VssBackupComponents.cs#L183-L194). [Overview of Pre-Backup Tasks](https://docs.microsoft.com/en-us/windows/win32/vss/overview-of-pre-backup-tasks) describes the “Prepare” and “Do” steps that you see in the above code. [Snapshot Creation Workflow](https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-vss-writer-backup-guide?view=sql-server-ver15#snapshot-creation-workflow) shows how the “Prepare” and “Do” run MS SQL through freeze and thaw. ---
**Author:** ![ccmgr](https://forum.duplicati.com/user_avatar/forum.duplicati.com/ccmgr/32/3770_2.png) [@ccmgr](https://forum.duplicati.com/u/ccmgr)\ **Post date:** [August 27, 2020, 10:26am UTC](https://forum.duplicati.com/t/yet-another-ms-sql-related-topic-sql-databases-vs-file-backup/10529/11 "2020-08-27T10:26:33Z")
Hi 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 ---
**Author:** ![drwtsn32](https://forum.duplicati.com/user_avatar/forum.duplicati.com/drwtsn32/32/2591_2.png) [@drwtsn32](https://forum.duplicati.com/u/drwtsn32)\ **Post date:** [August 27, 2020, 12:12pm UTC](https://forum.duplicati.com/t/yet-another-ms-sql-related-topic-sql-databases-vs-file-backup/10529/12 "2020-08-27T12:12:32Z")
> [@ccmgr](#): > > 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 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. ---
**Author:** ![ccmgr](https://forum.duplicati.com/user_avatar/forum.duplicati.com/ccmgr/32/3770_2.png) [@ccmgr](https://forum.duplicati.com/u/ccmgr)\ **Post date:** [August 27, 2020, 12:35pm UTC](https://forum.duplicati.com/t/yet-another-ms-sql-related-topic-sql-databases-vs-file-backup/10529/13 "2020-08-27T12:35:20Z")
Yes I am talking about database maintenance plans ran within the SQL Server and Getting Duplicati to backup the resulting .bak files ---
**Author:** ![bogdantomasciuc](https://forum.duplicati.com/user_avatar/forum.duplicati.com/bogdantomasciuc/32/4490_2.png) [@bogdantomasciuc](https://forum.duplicati.com/u/bogdantomasciuc)\ **Post date:** [August 27, 2020, 1:26pm UTC](https://forum.duplicati.com/t/yet-another-ms-sql-related-topic-sql-databases-vs-file-backup/10529/14 "2020-08-27T13:26:25Z")
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 🙂.