Backing up files in use, perhaps backing up on system shutdown

1

Hallo all:

I am volunteering in a small public library. We have 2 Windows PCs and an Ethernet NAS, where the backups are stored.

The library software uses a Gupta SQLBase database server. You need to stop the related Windows service before backing its files up. I am relatively new to the library, so I do not know more details yet. I guess there is a way to back up the database whilst still online with some clever SQL statement, but there is more software and more data on the PCs to backup anyway.

Years ago, the IT guy bought both Acronis True Image licenses and set up a full disk backup on shutdown. That has been working fine for years. But in the last months, both PCs randomly hang on shutdown, so I am looking for an alternative solution, because this commercial software has other issues too.

I am guessing that Duplicati has no easy, GUI way to run on shutdown, or does it? I found some forum questions, but nothing definitive on this.

I guess I could always use the Windows scheduler to run a task on shutdown, but I am worried whether such a backup would be consistent. If the backup happens before the database service has stopped, or while it is being stopped, the backups cannot be trusted. I wonder whether network services and so on are still available during shutdown for Duplicati to transfer the files.

An automated backup on shutdown is highly desirable. The library only opens a few hours per week, so the PCs are shutdown often. Library staff is exclusively made up of volunteers with no special IT abilities. Manually shutting down the database service before a backup means interrupting library operations. The unpaid staff is unwilling to do more manual tasks, and are prompt to leave the library at the end of the short opening hours. With the current backup solution, they leave the PCs shutting down, and they power themselves off after the backup is completed. Only the small NAS remains on.

Duplicati can apparently use Windows Volume Shadow Copies / Volume Snapshot Service. I do not think that Gupta SQLBase provides a VSS writer for it. Even if it did, the next software probably will not.

Unfortunately, I could not find any way to create a VSS snapshot late on shutdown or early on startup, or at a given time (say midnight, when the PCs are always off). Backing up files that are normally in use seems hard, or maybe I have not found the right documentation yet.

Backing up on shutdown would not be necessary if there no currently-open files. What I really need is to take a VSS snapshot from the filesystem at the time the computer is off (that is, between shutdown and restart). This way, no service or application can have any file open, and the backup would then be clean if the source is that VSS snapshot.

The future of VSS does not look rosy anyway. It looks like Windows 10 is replacing Volume Shadow Copies with a new feature called “File History”. Can Duplicati use it? Would it be possible to select the file history in the period that the PC was shutdown? This way, no files would have been open in this period.

Many thanks in advance,
rdiez

2

I don’t think backup on shutdown is the right approach. When a shutdown is requested, Windows notifies all applications that it’s about to happen. They only have a limited amount of time (some number of seconds) to shut down or acknowledge. If an application doesn’t respond quickly enough, it may be forced to close based on how the shutdown was initiated, or the user may be presented with an option to cancel or force the shutdown.

How about using the --run-script-before option to shut down the database engine, and then use --run-script-after to bring it back online?

Alternatively, some database engines (like MS SQL) allow you to schedule regular dumps/backups that the database engine itself creates. Duplicati could back up those dumps. I’m generally not a huge fan of 2-stage backups like this (what happens if the database dumps stop working, you may not realize you are backing up old dumps) but it is an option to get application-consistent backups.

I don’t see it going away. It is true that Windows 10 doesn’t expose a UI option for scheduling shadow copies on drives, but the underlying VSS system is definitely still there. Duplicati and other applications can use it. Windows Server versions have always exposed shadow copies in the UI and VSS is heavily used in the enterprise.

Good luck, and welcome to the forum!

3

I can’t speak about your next software, but SQLBase 12.1 enhanced database automation states:

Use VSS enabled backup tools to backup volumes with in-use active databases.

What (if anything) turns the database off at shutdown and back on at startup? You won’t need VSS snapshot if the database software is off, right? Guaranteeing that it’s off might be the difficult part…

4

Unfortunately, that only applies from SQLBase version 12.1, according to this roadmap I found:

And this particular library software comes with version 11.7. This is a waste of time anyway, because, like I said, the next software I come across with will probably not support VSS. For example, I doubt that open-source software originally coming from Unix will use such proprietary technologies. I am actually looking for an alternative backup solution where I do not have to research and care about VSS and its issues on a per-application basis.

Exactly, it is difficult to get that to work reliably. It is still not clear to me how Windows behaves on shutdown. That is why I am asking around. I would have thought that a backup software like this would “know” / document / provide tips about how to deal with backing up files currently in use.

5

I don’t think you’ll find anything. When it comes to application backups, this is something that has to be considered for every backup solution - even with high end enterprise backup products.

There are two types of backups you can do for application data files: Crash Consistent or Application Consistent.

Crash Consistent backups are easier to do, but they are not ideal. They may very well be “good enough” but it depends on the application. You’d definitely want to test restores if you try this approach:

  • Enable snapshot support in Duplicati so that it can back up open files. The application doesn’t need to support VSS directly - Duplicati is utilizing VSS at the filesystem layer so it can grab the raw data files.
  • Virtual Machine backups done at the hypervisor level can also be crash-consistent. (No idea if you are running a virtual environment.)

Application Consistent is best, but it requires more work. There are at least two options available to you with Duplicati:

  • Use pre/post scripts to stop/start the database engine before/after the backup job. When the engine is stopped, the files are in a known application-consistent state and are then unlocked so Duplicati can back them up without issue.
  • Use tools provided by your database vendor to perform dump-type backups, then let Duplicati back those files up. The dumps could be scheduled to run automatically. Test the restore procedure because it would be two-stage as well: restore the dumps, then use database vendor procedure to restore live databases from those dumps.

Other backup products may have additional options to create application consistent backups:

  • If the database vendor does have application-level VSS support, you might find a backup application that knows how to interface with it. (I think Duplicati only knows how to interface with MS SQL and Hyper-V at this time, but I could be wrong - I have not experimented with it.)
  • Some backup products may have an agent that knows how to speak to the database engine/application directly to perform backups/restores. This is more common in enterprise-grade backup solutions. Still requires setup, configuration, and testing.
1 Like
6

I don’t want to get very deep in VSS because the OP doesn’t have it and thinks they won’t in the future, however my impression is that it’s all automatic – provided the database supports VSS (and it works…).
–vss-exclude-writers is a Duplicati option that’s available to exclude faulty VSS writers from the backup.

Lacking VSS, the recourse for safe backup is to see what the database vendor says to do, then do that.

It’s not just Windows. You need to know how the database behaves. Perhaps your database docs say? There are no universal tips applicable to all situations. If you spot something close, link to give an idea.

VSS is as close to automagic as I know of. Discarding it, you most likely are on your own to script this. Duplicati has powerful scripting abilities, but cannot tell you exactly what the database will require of it.

Scripting options

https://github.com/duplicati/duplicati/blob/master/Duplicati/Library/Modules/Builtin/run-script-example.bat

–snapshot-policy does not require Windows VSS. It can run on Linux if you set things up properly, but I doubt the integration with Linux applications is as sophisticated and (somewhat) common as VSS has.

If you do find something that promises to backup your current and future database without any special planning, please also see what capabilities it has to do the restore and get the database back into use. Backing up is only part of the puzzle. As mentioned earlier “crash consistent” may not be good enough.

7

He does have VSS - for filesystem level backups anyway. So he could get crash consistent backups of the databases with minimal fuss. But I don’t know how well that database engine handles those types of backups.

Definitely abandon the idea of doing a backup on shutdown - that won’t work for the reasons I mentioned earlier.

8

That with the “minimum of fuss” is not true. “crash consistent” does not look safe enough. A backup is either safe or it is not. If you suddenly need to restore, you cannot hope that your database was consistent at the time of the backup. Even if you manage to script testing a restore, you cannot be sure that all database tables are really OK until the application tries to use the data. And I would have to look at all applications and their requirements.

I do not understand why this is so difficult. The PCs are shutdown every day. I cannot believe that Windows does not provide a way to generate a snapshot just before shutdown or just after startup, before anything else starts opening, locking and modifying files. It is a very simple concept, far simpler than trying to snapshot a filesystem that is currently running.

Failing that, I could live with the state of the files at last midnight, because I known the PCs are always off at midnight. But I haven’t seen yet any option to specify a filesystem snapshot based on a date/time.

I haven’t seen anything on Linux about this either (but I am not expert).

I am disappointed with the current state of operating systems / filesystems / documentation. This is a very simple concept.

The library has simple PCs, I cannot do anything fancy there. On other systems where I have virtual machines, I have an automated backup like this: shut the VM down at midnight, copy the virtual disk, and restart the VM. The backup is then perfect (I can afford the downtime).

9

Not true? It’s just a matter of changing a setting and making sure Duplicati runs with sufficient rights. It’s very easy. Whether or not crash consistent is sufficient is a different issue.

It depends. Some applications have a lot of resiliency built in to handle this type of situation, some do not. You won’t know for sure unless you test. But you aren’t required to go down this road. I’ve offered a couple other ideas that should work and get you application-consistent backups.

I explained the main issue in my earlier post. Perhaps you missed it. When a shutdown is initiated on Windows, it notifies all running processes of the intended shutdown and expects them all to shut down within a very short time frame. Duplicati would almost certainly not have time to complete a backup. Situation is even worse if the shutdown was initiated with the “force” option. It’s just not a good way to accomplish your goal.

10

I was actually thinking of suggesting this. The trick is to capture the entire system state as a package.
It’s too bad the simple computers you have in the library won’t let you do anything fancy such as VMs. Would these be Windows Server by any chance? That has vssadmin create shadow, if it helps any…

Taking a crash consistent snapshot will probably miss database info that is still in database’s memory even if somehow the files sitting on the drive are enough to do the restore according to design theory. Theory also doesn’t always translate to actual. Safest plan is to backup just as the application directs.

Using a VSS aware database will have the database quiet itself and flush its data out nicely to backup. Using a non-VSS-aware database can do the same thing (commonly) using whatever method it needs.

Taking a VSS snapshot when the database is down seems to add no value, as DB files are stable then. When database is down, you can just backup, and be application-consistent. Can you backup at start? Duplicati could start the database after the backup. Any idea how long it takes? Is it tolerable to users?

Doing it at logoff or shutdown might also be possible, if you’re willing to sequence things, e.g. database shuts down, Duplicati backs it up, Duplicati shuts down the PC. Tell the users what to do before leaving.

If you want a faster start, maybe robocopy at start before DB is up, and backup the copy at your leisure. Windows Task Scheduler can run task at Startup, Logon, or an arbitrary event, but it needs plan+doing.

Some do-it-yourself (if too ugly, give Microsoft feedback) ways to snapshot and read info back later are:

7 Tools to Copy Locked or In Use Files

Creating Volume Shadow Copies in PowerShell

Trying to mount a shadow copy in Windows 2012R2 but keep getting path not found.

Powershell: Get-Content without locking file

but AFAIK none of these will make an application-consistent snapshot without VSS-aware application. Neither they nor Duplicati can rummage around in the DB process and pull out a consistent snapshot.

11

None of the methods I have seen is actually satisfactory in this day and age. I think I have understood that quite a lot of manual intervention is necessary, like in the days of MS-DOS. I’ll have to figure out something manual.

About your suggestions: The library is using Windows 7 professional, hopefully Windows 10 professional soon. There is no incentive to pay for a Windows server license, as it provides no real benefit.

I could upgrade the hardware to run VMs, but the library software uses the server itself as another workstation (with GUI):

I don’t want to teach the users to use VM software or to connect with remote desktop. Even able volunteers should not need to deal with such complication.

In any case, vssadmin does not seen able to create a snapshot for a give date in the past, like the time when the computer was shutdown. There are no options to do a snapshot right before shutdown or right after startup, or at a given date. Taking a snapshot also takes time. I am guessing that NTFS is somehow no fast btrfs copy-on-write filesystem. I fear that doing a VSS snapshot manually on start-up will delay the boot process significantly. The volunteers tend come 5 minutes before opening time. Scheduling on shutdown is tricky, you need Group Policies and there are gotchas to learn.

Even if the embedded SQLBase version were VSS-aware (which is not), I do not really trust the vendor on this. Or the next vendor.

Backing up the database does take a few minutes. I could investigate a fast SSD though. And I have to be careful that the next library software update does not set the database service to autostart again.

It is hard enough to remind the volunteers to shut Windows down properly. Teaching them a special way for the server is not my preferred solution.

Damn, all that is missing is a command like this:

Schedule /On-Shutdown: vssadmin create shadow /right-before-power-off

An then on next reboot a background script can make the backup at leisure. And I would not have to worry at all what software is involved.

12

You can use Task Scheduler to create snapshots at specific events or times. NTFS snapshots are relatively quick, but it does have to talk to the VSS writers, so that can delay it slightly (on my system it takes less than 10 seconds to create a snapshot). btrfs snapshots has no such integrations that I’m aware of so it can create snapshots almost instantly - but again, it would be crash-consistent type of snapshot for in-use files.

If you are not on an Active Directory domain, then you can still schedule this without Group Policy. It would just require you doing it manually on each computer. Not sure how many computers you have in this environment.

Task Scheduler. :slight_smile: But unless the database engine was shut down, the snapshot would contain only crash-consistent copies of your database files.

Also, Duplicati cannot integrate with manually created snapshots - so I don’t know if it would help in that regard.

Regarding this application and its resilience with crash-consistent backups, what happens to these PCs if they are rebooted uncleanly? Does the database file get corrupt most of the time? If yes - it probably would be similar with crash-consistent backups. If no - you’d probably be just fine, and if it were me I’d schedule Duplicati to take a few backups throughout the day (using snapshots).