The difference between Interbase and Firebird

• Incremental backups - using nback utility in FB 2.x

Back-up is very fast (just dumping of changing pages), can use cascading (database -> monthly large snapshots -> daily deltas from last monthly -> hourls delta form last daily).

However it does not optimize (by recreating) the database. And it does not detect database errors other than top-level, errors of pages allocation (like "orphane page")

OTOH the pages snapshotted are mostly intact, so in case of partially corrupt database they might still have a manually salvageable data. If the corruption would be noticed quickly.

In a sense, that amounts to safe and incremental copying of he database file(s), wit hall the cons and pros.

• Usual backup via "gbak"

Reads data as a usual SNAPSHOT transaction, thus db errors would effect it. Some db erors would manifest it in read errors (like if DBA changed column type in a way incompatible with data), but other might result in some data being "invisible" and skipped.

Backup file is stripped of fast-access metadata and geta a lot smaller, which is good for archives (example: 1.1GB of raw database -> 380 MB FBK -> ~700 MB after restore).

In FB 2.x GBak is known to work considerably slower via TCP/IP than via Firebird Service Manager connection. It is told to be amended in forecoming FB 3.

Restore is basically recreating database, so it is slow. But it optimizes the database internal layout and saves some space (no more half-empty pages in the middle of the file).

Due to Firebird being very liberal in online (during active operations of users) scheme change (the safe approach was enforced in 2.0 but undone in 2.1 after uproar), the traditional backup might be "unrestorable", so the attempt at restoring a FBK file into a spare free disk is a must. Until you proven you can restore that backup, you may consider you don't have it.

• Tangential idea is Garbage Collection. Usually database have "peak hours" for example heavily used in day and almost unused at night. Sometimes DBA turn off garbage collection and reserve a lot of free space so during the day the database might grow almost uncontrollably. Then at night gbak is run not only to make a copy, but to enforce garbage collection at last.

• There also is a built-in redundancy: SHADOW SERVERS implemented back in Borland days. It does not protect from malicious users, virus, program or server bugs, etc. But in case one server crashes like OS or hardware fault, another one is ready to take his mission instantly.

OTOH is the main and shadow servers were in different networks (offices, cities, countries) and the link disappears between them, then one of the networks would see it as crash of the main server and another would see it as a crash of shadow server. When the link would be repaired, the databases would have new different conflicting data entered by users.

• This moves us to yet another idea of database replication. IOW the system in general might be designed to the idea tat there is more than single database/server, but they still have the same scheme and should periodically exchange data changes. That is a whole different topic, but it might be to some extent seen as a somewhat partial substitute to backups or crash defense.

PS. additionally you have to learn the difference between SuperServer (targeted at small installations) and Classic/SuperClassic servers. For running 24/7 the second options would be preferable, since frequently the server instances would be shut down after user disconnects. So while "server" as a concept keeps running 24/7, the actual executable programs of it get closed and restarted, easening at potential problems like memory leaks in server or UDFs. OTOH Classic server is more vulnerable to cache synchronization issues like in case of crash during Garbage Collection or attempts at metadata (scheme) changes while users are working.

In FB 3.x they promise to integrate those two approaches to make it a kind of sliding scale options in firebird.conf

Relatively green Firebird n00b here w/a fair PostgreSQL background, although specialized GIS work focused mostly to DB design and the sourcing/cleaning/making spatial multi-source data sets (usually of dubious quality) and then querying for map-able results in answer to specific location riddles. Toolbox is light, therefore, on Admin/Optimization/3rd Party Application Setup etc. You may (err, will) see some Firebird specific questions from me in the coming weeks. Thanks in advance! Took a stab at this slightly older Firebird question to get a feel for the new-to-me flavor of date handling and concatenation. Took more tweaking & testing than expected & my dabbling may not be the most efficient/elegant solution but, assuming the existence of column join_date in table employees, this will work:

SELECT Y ||' Years, '|| M ||' Months, and '|| D ||' Days'
FROM
(
SELECT
CASE WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
THEN datediff(year, join_date, current_date)-1
ELSE datediff(year, join_date, current_date)
END as Y,
CASE WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date)-1, join_date), current_date)-1
WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)>=0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date)-1, join_date), current_date)
WHEN datediff(year, join_date, current_date) = datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date), join_date), current_date)-1
ELSE datediff(month, dateadd(year, datediff(year, join_date, current_date), join_date), current_date)
END as M,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(day, dateadd(month, datediff(month, join_date, current_date)-1, join_date), current_date)
ELSE datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)
END as D
FROM employees
)

The subquery turned conditional on me when it became clear that: a) calculations on year & month don't consider the date as a whole; and b) date subtraction will happily return negative values regardless of the order of events.

Year/Month/Day Example: datediff() returns a value of 1 when retrieving the number of days, months, or years between 31.12.2015 and 1.1.2016.

Negative Value Example: about 69% of my test-date values are less than zero when I calculate the number of days less the number of days contained by whole months using:

SELECT datediff(day, dateadd(month, datediff(month, date_column, current_date), date_column), current_date) as D FROM date_table

We can streamline the subquery if substituting 365 days for one year is acceptable. The trick works with my test data but may trigger pitfalls with a broader range of dates. The simplified query looks like:

SELECT Y ||' Years, '|| M ||' Months, and '|| D ||' Days'
FROM
(
SELECT
datediff(day, join_date, current_date)/365 as Y,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(day, join_date, current_date)/365, join_date), current_date)-1
ELSE datediff(month, dateadd(year, datediff(day, join_date, current_date)/365, join_date), current_date)
END as M,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(day, dateadd(month, datediff(month, join_date, current_date)-1, join_date), current_date)
ELSE datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)
END as D
FROM employees
)

Holler if I goofed it or if I employed bad/overwrought logic. Hope it's helpful to someone at some point! Keep a lookout for new Firebird questions (might already have locked in struggle with a JDBC connection string that just will not lose).

Thanks and hello again! --Rob

I never had a plan-- Anonyman