MC418 Backup and Recovery of SQL Anywhere : Tips and Techniques

1
MC418Backup and Recovery of SQL Anywhere Tips
and Techniques
Reg Domaratzki International and Sustaining
Engineering Sybase iAnywhere Solutions rdomarat_at_sy
base.com
2
MC418 - Backup and Recovery of SQL Anywhere
Tips Techniques

• Points of Persistent Data
• Automatic Recovery
• Backing Up Your Database
• Backup Considerations in a Replicating
  Environment
• Recovery from Media Failure
• Backup Strategies

3
Points of Persistent Data

• Unlike some DBMS systems, ASA relies on the
  operating system to manage the files associated
  with maintaining data
• Main Database File
• DBSpaces
• Transaction Log
• Mirror Transaction Log
• All of these files are read and written to disk
  based on the page size specified on the dbinit
  command line

4
Main Database File

• The name of the main database file is specified
  on the dbinit command line
• It may or may not contain the a full path to the
  database file
• The main database file generally contains all the
  table pages and index pages associated with the
  database

5
DBSpaces

• A DBSpace is another database file that also
  keeps table pages and index pages
• There are two main reasons that DBspaces are used
• Performance - Putting index pages and table pages
  on separate devices can often speed up
  transactions that require index balancing
• 2GB File Limit - On FAT file systems, ASA will
  only support a file up to 2GB in size

6
The Transaction Log

• The transaction log is stored in a separate file
  and is also specified on the dbinit command line
• All changes to the database are stored in the
  transaction log in the order that they occur

7
The Transaction Log

• The transaction log should reside on a different
  device than the database file
• By default, the transaction log is put on the
  same device and in the same directory as the
  databasethis does not protect against media
  failure

8
Using a Transaction Log Mirror

• An identical copy of the transaction log
• Maintained at the same time as the transaction
  log
• Every time a database change is written to the
  transaction log, it is also written to the
  transaction log mirror file
• By default, a mirrored transaction log is not used

9
Why Use a Transaction Log Mirror?

• Media failure on the device where the transaction
  log resides can result in you losing your current
  transaction log
• Because changes are written to the transaction
  log before they are written to the main database
  file, you can lose your most recent data if your
  transaction log is lost

10
Why Use a Transaction Log Mirror?

• By mirroring your transaction log to a different
  device, you protect your most recent data in the
  case of media failure

11
Validating the Transaction Log on Database Startup

• The server checks that the transaction log and
  its mirror are identical by carrying out a full
  comparison of the two files on startup
• If the server finds that the transaction log and
  the mirror are identical up to the end of the
  shorter of the two files, the remainder of the
  longer file is copied into the shorter file

12
Validating the Transaction Log on Database Startup

• If the check finds that the log and the mirror
  are different in the body of the shorter of the
  two, one of the two files is corrupt
• The engine will shut down and the DBA will have
  to determine which log file is corrupt

13
Where are we?

• Points of Persistent Data
• Automatic Recovery
• Backing Up Your Database
• Backup Considerations in a Replicating
  Environment
• Recovery from Media Failure
• Backup Strategies

14
Automatic Recovery

• In order to ensure database recovery in the case
  of a system failure, ASA keeps two other logs
  internally in addition to the transaction log
• The Checkpoint Log
• The Rollback Log

15
The checkpoint log

• A checkpoint is an event that causes all dirty
  pages in memory to be written to disk
• A database file is composed of pages
• Before a page is updated (made dirty), a copy of
  the original is always made
• The copied pages are place in the checkpoint log
• Following a checkpoint, the checkpoint log is
  deleted
• The checkpoint log is physically located within
  the main database file

16
The rollback log

• Also stored in the main database file
• There is a separate rollback log for each
  connection
• Every time a transaction is started, the rollback
  log will contain the SQL statements needed to
  undo the transaction in case a ROLLBACK command
  is executed
• The rollback log for a given connection is
  deleted once a COMMIT or ROLLBACK is executed

17
Recovery from system failure

• After a power failure or other system failure you
  should run the system disk verification program
• This should be done before running any other
  software.
• NetWare Load the Novell VREPAIR NLM to repair
  any volume that will not mount due to errors.
• UNIX Use chkfsys
• Windows Run chkdsk /f or scandisk
• After a system error occurs, the server recovers
  automatically when you restart the database

18
Steps to recover from a system failure

• Adaptive Server Anywhere automatically takes
  three steps to recover from a system failure
• Restore all pages to the most recent checkpoint,
  using the checkpoint log
• Apply any changes made between the checkpoint and
  the system failure. These changes are in the
  transaction log
• Roll back all uncommitted transactions, using the
  rollback logs
• Step 3 may take a long time if there are long
  uncommitted transactions that have already done a
  great deal of work since the last checkpoint

19
Where are we?

• Points of Persistent Data
• Automatic Recovery
• Backing Up Your Database
• Backup Considerations in a Replicating
  Environment
• Recovery from Media Failure
• Backup Strategies

20
Backing up your Database

• Backup Terminology
• Validating your Database
• Full Backups
• Incremental Backups

21
Backup Terminology

• Full backup
• Makes a copy of the database file and
  (optionally) a copy of the transaction log
• Incremental backup
• Makes a copy of only the transaction log
• Online backups
• Backups can be made without stopping the server
• Offline backups
• Database files are copied while the database
  server is not running
• Image Backups
• Copies the database and/or transaction log to
  another directory
• Archive Backups
• Creates a single file holding all required backup
  information
• Can be a single file or a tape drive device name

22
Check the validity of the database

• Before doing a full backup, it is a good idea to
  verify that the database file is not
  corrupt dbvalid -c "uiddbapwdsql
• Dbvalid can be used to validate either the
  indexes, data or both the indexes and data of
  your databases
• Index Validation ensure that every index record
  has a corresponding entry in the table and vice
  versa
• Data Validation scans all non-indexed columns to
  ensure that the data is readable.
• The default is to only do index validation

23
Performing a full backup

• A full backup is completed offline by copying the
  database file(s) and optionally the transaction
  log to the backup media
• As -d or -t is not specified, database file and
  transaction log are backed up dbbackup -c
  "uiddbapwdsqldbfpath\asademo.db"
  e\backup where path is the name of your
  Adaptive Server Anywhere installation directory

24
Running dbvalid on backed up database files

• Do not run dbvalid on a database file that you
  have just backed up
• dbvalid will start an engine with the database
  file specified and once it it done, will shut
  down
• Shutting down the engine causes a checkpoint
  which will then modify the most recently backed
  up transaction log offset

25
Backup Utility Transaction log options

• Delete and restart the transaction log (-x )
• the existing transaction log is backed up, then
  the original is deleted and a new transaction log
  is started with the same name
• Rename and start new transaction log (-r )
• Step 1 A copy is made of the current working
  transaction log file and saved to the directory
  specified in the command line
• Step 2 The current transaction log remains in
  its current directory, but is renamed. This file
  is then no longer the current transaction log
• Step 3 A new transaction log is generated that
  contains no transactions. It is given the same
  name of the previous current transaction log and
  is used by the database engine and dbremote as
  the current transaction log
• Back up the transaction log file only (-t )

26
Keep several full backups

• If you back up on top of the previous backup, and
  you get a media failure in the middle of the
  backup, you are left with no backup at all
• You should also keep some of your full backups
  offsite to protect against fire, flood,
  earthquake, theft, or vandalism

27
Performing an incremental backup

• An incremental backup is a copy of the
  transaction log dbbackup -c "uiddbapwdsql
  dbfpath\asademo.db" -t e\backup
• The transaction log has all changes since the
  most recent full backup
• You can carry out an offline incremental backup
  by making a copy of the transaction log file.
  Alternatively, you can carry out an online
  incremental backup by running the backup utility
  and backing up just the transaction log

28
Deleting the transaction log

• You can choose to archive and delete the
  transaction log if the transaction log tends to
  grow to an unmanageable size between full backups
  and you do not want to get a larger storage
  device or do more frequent full backups
• There is a drawback to archiving and deleting the
  transaction log after a daily backup
• If you have media failure on the database file,
  there will be several transaction logs since the
  last full backup
• Each of the transaction logs needs to be applied
  in sequence to bring the database up to date

29
Archive Backups

• Instead of backing up the database file and
  transaction log to another directory, all
  database files can be backed up into a single
  file that can be stored on tape
• Only one file can be stored on each tape
• Meant for backing up very large databases
• In the first releases of v603 on UNIX, we forgot
  to ship a shared library that was needed to
  perform archive backups to tape
• The 603 EBF has the required files, as does all
  releases on v7

30
Scheduling Backups to Run

• In v7, you can now use event scheduling to
  automatically back up your database
• create event IncrementalBackup
• schedule
• start time '100 AM' every 24 hours
• handler
• begin
• backup database directory 'c\\backup'
• transaction log only
• transaction log rename match
• end

31
Overview

• Points of Persistent Data
• Automatic Recovery
• Backing Up Your Database
• Backup Considerations in a Replicating
  Environment
• Recovery from Media Failure
• Backup Strategies

32
Backup Considerations in a replicating environment

• There are a few other things to consider in a
  replicating environment
• Protecting the current log files on the
  consolidated database
• Backing up old log files
• Backing up the messaging system
• dbremote -u
• Recovery options on the consolidated

33
Protecting the Current Log File

• When dbremote runs, it will always scan the
  current transaction log
• If your current log file is lost, and dbremote
  has sent messages from a missing log file, you
  will have to re-extract all remote users that
  pick up the messages generated from the now
  missing log file

34
Protecting the Current Log File

• It is extra important to make sure that there is
  a mirror log file on a separate device from that
  where the database file and log file reside
• This will protect you from media failure on a
  single device

35
Backing up Old Log Files

• In order to manage your log files, you will often
  rename and re-start your transaction logs
• Until every remote user has confirmed every
  transaction in a log file, you cannot delete the
  old logs, and dbremote will need to know where
  these old log files are located
• It is a good idea to backup these old log files
  to alternate media in case of media failure

36
Backing up Old Log Files

• Keeping old log files should be considered
  completely separate from your backup and recovery
  procedures
• Just the old renamed log files cannot guarantee
  recoverability unless a valid backup strategy is
  also in place

37
Backing up the messaging system

• There is no need to back up the messaging system
• The guaranteed delivery system that is
  implemented by dbremote will guarantee that any
  messages lost as a result of system failure or
  media failure will be resent

38
DBREMOTE -u

• Running dbremote with the -u option will force
  dbremote into only sending transactions that
  occur in the off-line logs
• Therefore, if the current transaction log (and
  mirror log) are both lost, you can guarantee that
  no remote users have received messages from the
  lost log and nobody will need to be re-extracted

39
DBREMOTE -u

• This will slow down the speed with which your
  remote sites receive data from he consolidated,
  but will guarantee that remotes need not be
  re-extracted should the current log be lost

40
Where are we?

• Points of Persistent Data
• Automatic Recovery
• Backing Up Your Database
• Backup Considerations in a Replicating
  Environment
• Recovery from Media Failure
• Backup Strategies

41
Recovery from media failure

• Recovery from media failure requires you to keep
  the transaction log on a separate device from the
  database file
• Recovery depends on whether the media failure is
  on the device holding your database file or on
  the device holding your transaction log
• The first step in recovering from a media failure
  is to clean up, reformat, or replace the device
  that failed
• There are some special considerations if you are
  recovering a consolidated database

42
Media failure on the database file

• If your transaction log is still usable, but you
  have lost your database file, the recovery
  process depends on the number of transaction logs
  you have backed up since your last full backup

43
If you have a single transaction log

• If you have not deleted or restarted the
  transaction log since the last full backup, the
  transaction log contains everything since the
  last backup. Recovery involves four steps
• Make a backup of the transaction log immediately.
  Since the database file is gone, transaction log
  is the only record of changes made to the
  database
• Restore the most recent full backup of the
  database file
• Start the server with the (-a) switch, to apply
  the transaction log and bring the database up to
  date. The engine will shut down after the log
  file has been applied. This is normal
• dbeng7 asademo.db -a asademo.log
• Start the database normally. The server will come
  up and any new activity will be appended to the
  current transaction log

44
If you have multiple transaction logs

• If you have archived and deleted the transaction
  log since the last full backup, each transaction
  log since the full backup needs to be applied in
  sequence to bring the database up to date
• Make a backup of all transaction logs immediately
  
• Restore the most recent full backup (the database
  file)
• Starting with the first transaction log after the
  full backup, apply each archived transaction log
  by starting the server with the (-a)
  switch dbeng7 asademo.db -a first.log dbeng7
  asademo.db -a second.log
• Do not apply the transaction logs in the wrong
  order or skip a transaction log in the sequence
• Start the database in the normal way and any new
  activity will be appended to the current
  transaction log

45
Media failure on the transaction log

• If your database file is still usable but you
  have lost your transaction log, the recovery
  process is as follows
• Make a backup of the database file immediately.
  The transaction log is gone, and the only record
  of the changes is in the database file
• Restart the database with the -f switch dbeng7
  asademo.db -f
• The server will restore the database to the most
  recent checkpoint and then roll back any
  transactions that were not committed at the time
  of the checkpoint
• A new transaction log will be created

46
WARNING!!!!

• NEVER NEVER NEVER NEVER NEVER use the -f recovery
  switch on a consolidated database

47
Consequences of media failure on the transaction
log

• Media failure on the transaction log can have
  more serious consequences than media failure on
  the database file
• If you lose the transaction log, all changes
  since the last checkpoint are lost
• This will be a problem if you have a system
  failure and a media failure at the same time such
  as a power failure that causes a head crash that
  damages the disk

48
Recovering uncommitted database changes

• The dbtran utility has a command line option (-a)
  to translate transactions that were not committed
  
• There is also a command line option (-f) to
  output only from the most recent checkpoint
• dbtran -a -f sample.log changes.sql
• With these option, you can recover changes that
  were not committed by editing the SQL command
  file and picking out changes that you want to
  recover
• If the -a option is not chosen, the log
  translation utility omits transactions that were
  rolled back

49
Using a live backup for machine redundancy

• You carry out a live backup of the transaction
  log by using the dbbackup command line utility
  with the -l command-line option
• Live backups provide a redundant copy of the
  transaction log that are available for restart of
  your system on a secondary machine in case the
  machine running the database server becomes
  unusable
• A live backup runs continuously, terminating only
  if the server shuts down

50
Recovery Options on the Consolidated

• Anytime a recovery procedure involves deleting a
  transaction log or using the -f switch, this
  database can no longer be used in a replicating
  environment
• Any event that destroys the log file or modifies
  the current log offset will cause problems
• You should always be able to go to your last
  valid backup and apply transaction logs up to the
  point of the failure

51
Recovery Steps for Consolidated Databases

• Loss of Database File
• Loss of Log OR Mirror Log File
• Loss of Log AND Mirror Log File
• Loss of a Renamed Log File

52
Loss of Database File

• Revert to a backup copy of the database and apply
  the logs incrementally to bring the database up
  to date dbeng7 -a ...

53
Loss of Log OR Mirror Log File

• If only one of the Log or Mirror Log file is
  lost, then recovery consists of replacing the
  missing/damaged file with the remaining good file

54
Loss of Log AND Mirror Log File

• When both the Log and Mirror Log files are lost,
  recovery consists of reverting to the last full
  backup of the database and applying the available
  logs to bring it up the point of the most recent
  incremental backup
• Since we have been running dbremote -u we can
  continue replicating from this point
• Any transaction which occurred directly on the
  consolidated will have to be recreated
• Any transactions which occurred as a result of
  dbremote will be resent by the remotes

55
Loss of a Renamed Log File

• If a corresponding renamed Mirror Log file is
  available, then you can recover by copying this
  file in place of the missing renamed Log file
• If a renamed mirror log file is not available,
  then the renamed Log file can be recovered from
  your tape or alternate media backup

56
Where are we?

• Points of Persistent Data
• Automatic Recovery
• Backing Up Your Database
• Backup Considerations in a Replicating
  Environment
• Recovery from Media Failure
• Backup Strategies

57
Backup Strategies

• There will be different levels of paranoia based
  on how you are using ASA
• Unconcerned
• Concerned
• Paranoid
• Job depends on backup

58
Unconcerned Backup Strategy

• Physical Database Setup
• Database File and Log File reside on same
  physical device, probably in the same directory
• Might consider running database engine with the
  -m switch to manage the size of the transaction
  log
• Backup Procedure
• No need to do anything
• You could possibly take a full system backup to
  tape every now and then

59
Unconcerned Backup Strategy

• Pros
• Easy setup, no maintenance required
• Protected from system failures
• Cons
• No protection from media failure
• Recovery of database from media failure depends
  on system backup
• Changes since last system backup are lost even if
  system backup exists

60
Concerned Backup Strategy

• Physical Database Setup
• Database file and transaction log reside on
  different physical devices
• Backup Procedure
• Take a full backup every week and rename and
  restart the transaction log
• Either have the backup placed on another device
  (network mapped drive), or spin the backed up
  database and transaction log to alternate media
  (tape, jaz drive, ) after the backup completes
• Test your recovery procedure after having defined
  your procedure

61
Concerned Backup Strategy

• Pros
• Protection from both system and media failure on
  a single device
• No data loss should a media failure occur on a
  single device
• Cons
• You might be overwriting your only good backup,
  so a backup failure may result in you not having
  any backup at all
• Media failure on device with transaction log
  could result in data loss since the last
  checkpoint

62
Paranoid Backup Strategy

• Physical Database Setup
• Database file, transaction log and transaction
  log mirror all reside on separate physical
  devices
• Backup Procedure
• Run dbvalid on database and check for errors
• Take a full backup once a week and an incremental
  backup every day, and rename and restart the
  transaction log
• Copy backed up database file and transaction logs
  to another machine or alternate media once backup
  completes
• Test your recovery procedure once a month

63
Paranoid Backup Strategy

• Pros
• Protection from system and media failures
• Running dbvalid will help you from backing up a
  corrupt database
• Placing backed up files on alternate media will
  give you a point to recover to should the entire
  machine be destroyed
• Cons
• Running dbvalid on large database is time
  consuming
• Problem with disk controller could destroy all
  hard drives
• Lots of backed up files to manage
• Database may become corrupt during backup

64
Job Security Backup Strategy

• Physical Database Setup
• Database file, transaction log and transaction
  log mirror all reside on separate physical
  devices
• Each physical device is controlled by a separate
  disk controller from a different manufacturer

65
Job Security Backup Strategy

• Backup Procedure
• Run dbvalid on database and check for errors
• Take a full backup once a week and an incremental
  backup every day, and rename and restart the
  transaction log
• Copy backed up database file and transaction logs
  to alternate media once backup completes
• Move alternate media off-site as soon as possible
• Run dbvalid on a copy of the backed up database
  to ensure that backed up image is valid
• Test your recovery procedure at least once a week
• If running dbremote, use the -u switch
• Also consider running dbbackup -l (live backup)
  to keep an up-to-date version of your log file on
  a separate machine

66
Job Security Backup Strategy

• Pros
• Protection from system and media failures
• Running dbvalid twice ensures that backed up
  image is also valid
• Taking alternate media off-site protects you from
  a site disaster
• Running dbremote with the -u switch ensures that
  in the case of a site disaster or system and
  media failure, no remote users will be affected
• A bug in a disk controller can not destroy all
  your hard drives

67
Job Security Backup Strategy

• Cons
• In the case of a site disaster or system and
  media failure, there will still be data loss
  since the last backup
• Very time consuming
• Lots of backed up files need to be managed

68
Summary

• Points of Persistent Data
• Automatic Recovery
• Backing Up Your Database
• Backup Considerations in a Replicating
  Environment
• Recovery from Media Failure
• Backup Strategies