Subject RE: [firebird-support] Backup - Restore
Author Alan McDonald
> Hi
> When performing a Backup - Restore on a DB, the fysical size of the
> file always drops.
> Why is it, that it is this way ?

this is a result of dynamic sizing the database file. The server provides
for additional size when you make it grow with INSERTS. When you DELETE a
lot of records, the records are marked as deleted but the space is not
reduced so the server doesn't have to grow the file again when you next
INSERT. This is as designed and increases overall performance.
(PS. MSSQL used to be such teh DBA had to keep a close eye on the file and
make sure the users weren't growing past the expected/alloted size - it
lacked a the dynamic sizing feature which IB had from the start).

> Can I do something similar to db without having to go through a
> Backup - Restore ?

Nothing really replaces a good backup/restore cycle (there's a lot going

> The reason I ask is, that every month or so we do a backup - restore.
> The restore being done here makes the DB go faster than before.
> But I can't figure out why.

I've been using IB and now FB for many many server years now and all my
tests and experience has not shown me that the backup/restore cycle lifts
performance noticeably.
I can imagine that if I inserted serveral gigbytes of data one day, then
deleted these several gigabytes the next, then perhaps this might make a
difference but I have not tested such enormous quantities.
Perhaps also, if you have poor index planning in the first place, then you
may experience poor performance after large insertions, then better
performance after backup/restore. But with good indexing, there should not
be any noticeable difference.

> My transactions are always nice and close to each other.

that's good
> Michael
> PS.
> I do a Backup each night.
> But I NEVER do a automatic RESTORE.
> This I do manually once a month!

That's good too and never restore over the top of the real database. Always
do it to a copy, then rename the old to another filename and then the
restore to the proper filename. Do all this while noone is accessing the
database. Even better is to start the process by renaming the real db to
another name first. then start the backup/restore cycle.