Backup of live database

[bjanker77]

Hi.

Our most used backup schedules is using the maintenance plans with a schedule each morning, afternoon and night. We are creating a set of 3 backup images in 3 separate folders. However, our biggest customer insists on having backups 4 times each day. At 01, 12, 18 and 21. The backups at 01, 18 and 21 usually finishes in 7-10 minutes (45 GB database). But the one at 12 o'clock tends to take very long time, due to high activity in the database in the middle of the day. Yesterday it used over 5 hours and last saturday (which is normally a quiet day) it was still running at 18. But it finished as soon as I disconnected a user which obviously was causing a lock.

Is there any way to discover which connections that are preventing the backup from getting done? Or perhaps even get some sort of notification from SQL Anywhere that the backup is hanging?

Or perhaps we're using a wrong approach to the whole backup routine?

Any suggestions is very welcome. 🙂

regards,

Bjarne

[VolkerBarth]

So are these all full backups (i.e. database file(s) and transaction log are copied)?

I guess it's way more common to do a full backup, say, once a day (probably at a time with low usage like at night), and then incremental backups (i.e. just the transaction log) quite often (say, every quarter/half an hour or the like).

Backups are influenced by current transactions, see the WAIT BEFORE/AFTER clauses. If the full backup is blocked by current transactions, I suspect that these options are not optimally chosen - however, the backup options do also influence whether you can validate the backup copy itself and how to do a restore, so that should fit together.

[VolkerBarth]

FWIW, you have chosen the tag "live-backup" - do you really relate to the "live backup" facility, i.e. running DBBACKUP -l ...?

[bjanker77]

Hi.

I guess the "live-backup" tag was not quite right in this scenario. We're talking a full backup with db and log files yes.

I will certainly look into the incremental backups, at least the one in the middle of the day.

Thanks,

Bjarne

[Vlad]

if they want real-real copy of the DB, would it make sense and use "Database mirroring"? Then they will get a 1-to-1 copy of the DB in real time.

[Breck_Carter]

You probably mean "online backup" which is a backup taken while the database is running. The term "live backup" is actually a thing: a continuous backup taken for high availability purposes.

Online backups don't care about ordinary locks, but they do care a lot about the WAIT AFTER END clause: "Use this clause with caution as new, incoming transactions can cause the backup to wait indefinitely."

Personally, I have never been able to use WAIT BEFORE START or WAIT AFTER END successfully in production... works fine in test, though 🙂

[Breck_Carter]

FWIW it is possible to write SQL code to drop all other connections. This code could be enhanced to only drop other connections that have running transactions... or to send emails to Security about the offenders 🙂

[VolkerBarth]

Personally, I have never been able to use WAIT BEFORE START or WAIT AFTER END successfully in production.

Yes, been there, too. Unfortunately, the maintenance plans as created within SQL Central do use the WAIT BEFORE START clause by default, so that may affect Bjarne, too. - I had to drop that clause when using maintenance plans.

[VolkerBarth]

OK, then I'll remove that tag from your question...:)