cancel
Showing results for 
Search instead for 
Did you mean: 

V16 backup plan

2,916

The objective is to have a backup of a relatively large (30gb) database that can be restored rapidly, with a very narrow window of potential data loss, as this database is in use 24x7x365. This should all be documented in such a manner that we can assume the entire IT team was beamed up by aliens and the janitor is responsible for bringing the database back up.

There is much helpful information on this site. I've read enough now that I've started to second guess the plan. What I've outlined below seems to work just fine, but I'm sure someone can advise a better way. Database mirroring is coming, but not until the first part of next year.

We have 'ServerA' which is running the database, and 'ServerB', which will be the backup target. ServerB is maintained at the same patch level, etc as ServerA. Both servers are 64bit Windows.

On ServerB there is a batch file that uses DBBACKUP to create a full backup. For now it runs once a week. It's a pretty straight forward commnand:

dbbackup -c "DSN=MyDSN;" -x -y c:\\backup\\full


Another batch file runs every 30 minutes to grab the transaction log

dbbackup -c "DSN=MyDSN;" -t -y c:\\backup\\incr


Should we need to use the backup -- which implies that all three of my VMWare HA hosts are down and the redundant iSCSI sans have failed:

  1. Make new "working folder" for the recovery server and switch to it
  2. copy files from c:\\backup\\full to it
  3. apply the transaction log from the full backup
    DBSRV16 mydatabase.db -a mydatabase.log
  4. Exercise patience until the server shuts down
  5. Copy mydatabase.log from c:\\backup\\incr
  6. Repeat steps 3 & 4
  7. miller time
VolkerBarth
Contributor
0 Kudos

If you want to have a fast restore, wouldn't a live backup from server B do the trick? (Nowadays a live backup seems inferior to a true HA system, but it requires less configuration - and you seem to have the necessary backup server already.)

Note: A live backup would not require most of the above steps, and particularly the mere copy of a 30 GB database file may take some time...

0 Kudos

I considered that, but it looked to me like a live backup requires an interactive session. i.e., someone has to be logged into to the Windows desktop on the backup server. And, I don't recall seeing any guidance on what can happen when the live backup is interrupted. However, this is why I asked the question here. I'll revisit the documentation..

0 Kudos

The other question I had was whether applying the transaction log from the initial full backup is required prior to applying the most recent transaction log, since the only time the log is truncated is when the full backup is accomplished.

VolkerBarth
Contributor
0 Kudos

You might also have a look at the discussions on HA and live backup in this forum, such as those tagged with "live"...

As to the required session: I guess (but do not know) that dbbackup -l could be run as a service, as well.

FWIW: How near/far are both servers located? How are they connected? Is it possibly to ship the (weekly) full database backup easily and fast to the backup server. (AFAIK, that would be necessary for a live backup, when a full database backup is performed.)

VolkerBarth
Contributor

I have learnt to apply both logs, and that's also the recommended method according to the docs for incremental backups.

In my understanding, that's because the original log may have hold transactions that have been in progress during the full backup.

justin_willey
Participant

Yes, it's essential - as I leant to my cost some years ago!

Breck_Carter
Participant

> whether applying the transaction log from the initial full backup is required prior to applying the most recent transaction log

When making a full backup of a running database and transaction log, the backup database file is a copy of the database file as it stood at the START of the backup process, whereas the backup transaction log is a copy of the database file as it stood at the END of the backup process. So, if changes were made while the backup process was running, they'll be on the backup log but not in the backup database... and that backup log file will be required for a future restore of the backup database file.

The challenge is, that's not the case with an idle database so folks can be lulled into the belief the backup log file is unecessary. See Quiz: Which log backups are required to restore a database?

Breck_Carter
Participant
0 Kudos

> to my cost

So many of the worst horror stories involving backup, restore, replication and synchronization include statements to the effect that a transaction log was deleted because it was "no longer needed".

What is YOUR story?

justin_willey
Participant
0 Kudos

Luckily it wasn't disastrous as WHEN we realised what we were doing wrong we were able to recover and apply the relevant log.

Before that happened we wasted a lot of time trying to work out what was wrong - we had lots of tests that worked fine, but this one db that we couldn't apply the logs to. Of course, the difference was that in the other cases no transactions had taken place during the backup - so there was no gap in the transaction sequence.

VolkerBarth
Contributor
0 Kudos

Given all this advice (and "success" stories) here, it seems "The other question I had" should be raised as its own valuable question, partly as the docs do not explain that in detail, AFAIK...

justin_willey
Participant
0 Kudos

Might be worth adding to your list of durrh Characteristic Errors http://sqlanywhere.blogspot.ca/2014/02/characteristic-errors-revision-5.html

View Entire Topic
Breck_Carter
Participant

> a very narrow window of potential data loss, as this database is in use 24x7x365

> entire IT team was beamed up by aliens and the janitor is responsible for bringing the database back up.

Those two requirements are reasonable for a high availability setup, not backup and recovery... not even live backup. Experience has shown that backup is easy but recovery is hard because you can never predict exactly what the problem's going to be.

You always need a backup and recovery plan, and it should be tested, but don't expect it to provide high availability... for that you need... wait for it :)... high availability.

FWIW a dbbackup -l live backup command can be wrapped in a GOTO start loop that repeatedly runs the command if it fails. The usual cause for failure is loss of contact with the master database server... if it is intermittent then the GOTO loop will let dbbackup reconnect and continue on... if the loss of contact is permanent then the GOTO loop will continue until you kill it, which you will probably do because it's time to start the secondary server and apply the live log.

All that live backup stuff is possible, but for V16 it's the Amish way of doing high availability... no offense to the Amish, or to live backup, or SQL Remote, or any of The Old Ways... supported by all but embraced by few.

Remember that when the live backup is used for failover you have to (a) start the second server and (b) apply the log... and if it's been a while since a full backup has been taken, applying the live log may be time consuming.

Plus, it's harder and more time consuming to practice a live log failover than an HA failover. Janitor-ready documentation is a real challenge, far beyond my abilities (I tried, with V9, failed).

0 Kudos

I may have drawn an inaccurate picture. The primary server is running in VMWare with HA, connected to redundant iSCSI SANs, with geographic separation of redundant devices. So the primary mission for this exercise is protection from 1) catastrophic hardware failure and 2) severe database corruption.

The 'janitor can do it' factor comes from ISO auditor who believe that everyone can do any job in the factory given good enough instructions. So mostly it's an exercise to make sure a box can be checked off.

Breck_Carter
Participant
0 Kudos

Please post a new question... a backup and recovery plan for a geographically separate HA setup is an entirely different proposition. In particular, you do not want any of your regular backup activities to interfere with regular HA operation or even HA failover.

Is it possible to check the box without having an actual janitor demonstrate recovery from catastrophic failure? Without testing, recovery is guaranteed to fail... not 99.9%, but 100%.

0 Kudos

It's ISO, so "checking the box" mostly means that we show that we have the instructions available, and have confirmed that if followed, they will work -- janitor not required. Even as we speak, I am doing testing and documentation of the recovery process.

In this instance, I'm using 'HA' in the VMWare context, which will fail a VM over from one host to another. This we have tested (both intentionally and not so much), and it works well -- the most users noticed was a 10 second lag on a couple screens on the ERP system. 'Geographically separated', for now, means the hardware is at the other end of the building, literally a 1/3 of a mile away, with gigabit fiber between.

In reality, the process I am documenting is for a "the building burned down" scenario. Since we are a plastics manufacturer, in that case I'll have more important issues than if I have an exact up to the minute inventory.