We have a Firebird database that's almost 200 Gbyte in sice, and for performance we have forced writes off. Please don't debate the risks of forced writes, we are aware of them. This is Firebird 3.0.4 on Windows Server 2016.
For backup, we use alter database begin backup
and alter database end backup
, and copy the main database file using FastCopy. We can see that the delta file is created right away when executing alter database begin backup
. But the main database file usually gets an updated timestamp quite some time later, often within a few minutes, but sometimes it takes longer.
I assume this is caused by forced writes off and the fact that Windows may delay some writes for an arbitrary amount of time. In other words, I assume that the Firebird engine does in fact not write to the main databse file after alter database begin backup
, but writes that were made before this may be delayed by Windows for quite a while, meaning it's not in fact safe to start copying the main database file until Windows has flushed all writes.
Now, my question is how to properly handle this to achieve safe and reliable backups?
Up to now I've scheduled file copy to 3 hours after alter database begin backup
, and I also included a dummy transaction right after the alter database begin backup
.
But are there better approaches?
I came up with the idea to use gfix
to switch forced writes on before executing alter database begin backup
(and switch it back off later). I assume this will cause the locked state to be flushed to disk right away, but I also assume that writes that were made before switching forced writes on will still suffer the arbitrary delay from Windows' write cache. Correct? Or is gfix
or the Firebird engine actually able to force flush all previous writes that are already in the Windows write cache?
Another idea is to use Sysinternals Sync util to flush Windows' write cache for the entire disk. For overall system performace, this would not be a problem, considering backup is scheduled to a low-traffic time of day.
We could use nbackup instead of FastCopy. Would this help? In other words: would nbackup's reads of the main database file see the new still-cached versions of the database pages being copied, or would it see the outdated on-disk versions?
In fact, I'm not sure if FastCopy actually sees the new still-cached versions or not, but it fails when it notices that the source file's timestamp has been changed since it started copying, so it fails anyway. There's no apparent way to avoid this.
alter database begin backup
to flush the file to the disk, otherwise it won't work as advertised. In any case, I'm not sure you'll get a suitable answer here.