Sybase IQ Administration
Backup and Restore
Copyright 2009 Sybase, Inc. All rights reserved.
No part of this publication may be reproduced, transmitted, or translated in any form or by any means,
electronic, mechanical, manual, optical or otherwise, without prior written permission from Sybase, Inc.
MODULE TOPICS
Backup
10 - 2
Restore
Diagnostic Stored Procedures
Utilities: dblog and db_backupheader
Two Ways To Backup Sybase IQ Data
Database backup
Full backup makes a complete copy of the database
incremental backup copies all transactions since the last
backup of any type copies all transactions since the last full
Incremental-since-full backup
Virtual Backup copies all of the database except for the
table data from the IQ Store
Uses multiple devices for backups, allowing for parallel
backups
Can run concurrently with other database operations, except
metadata changes
The process backs up committed data
10 - 3
Database Backup Methods
Full
Full backup of Catalog Store
Full backup of IQ Store
Default action
Incremental
Full backup of Catalog Store
Backs up changes to IQ Store since last backup of any type
Incremental-since-full
Full backup of Catalog Store
Backs up changes to IQ Store since last full backup
10 - 4
Backup Process Steps
First, the Catalog Store (dbname.db) is backed up for a database, then
the transaction log file (dbname.log only when the database is SA),
and then all the dbspace files for the IQ Store
Backs up ONLY committed data!
10 - 5
Archive Devices
Archive devices can be either disk or magnetic tape drives
Disk backups must go to file systems, not raw devices
Supported tape devices:
Digital linear tape (DLT) on UNIX
4mm DDS
8mm
10 - 6
Stacker drives with multiple tapes
BACKUP DATABASE Syntax
BACKUP DATABASE
... { READWRITE FILES ONLY |
Sybase IQ 15 Additions
READONLY dbspace-or-file [, ]}
...
...
...
...
...
[CRC ON | OFF]
[ATTENDED ON | OFF]
[BLOCK FACTOR integer]
[{FULL|INCREMENTAL|INCREMENTAL SINCE FULL}]
[ { VIRTUAL DECOUPLED |
VIRTUAL ENCAPSULATED 'shell_command' } ]
... TO 'archive_device' [SIZE #_of_KB integer ]
[ STACKER #_of_tapes_in_stack integer ] ...
... [WITH COMMENT string]
10 - 7
Which Files and Where
DBSPACES | FILES
Sybase IQ 15.0 introduces the concept and implementation of
one or more dbfiles within a dbspace
Accommodates the architectural enhancement
Dbfiles are not present in Sybase IQ 12.7
TO
Specifies the name of the archive_device to be used for
backup, delimited with single quotation marks
Archive_device is a file name or tape drive device name for
the archive file
Must use separate TO clauses if using multiple archive
devices
10 - 9
Attended/Unattended
Attended
Operator must be present
Prompts to mount archive media
Default action
Unattended
No prompts are issued
Must make appropriate size estimates and set up devices in
advance
10 - 10
Backup Options
FULL
Specifies a full backup and is the default action
All blocks in use in the database are saved to the archive
device(s)
INCREMENTAL
Specifies an incremental backup
All blocks changed since the last backup of any kind are
saved to the archive device(s)
INCREMENTAL SINCE FULL
Specifies an incremental backup
All blocks changed since the last FULL backup are saved to
the archive device(s)
10 - 11
Continued
Backup Options
VIRTUAL DECOUPLED
Specifies a decoupled virtual backup
For the backup to be complete:
Copy the IQ dbspaces after the decoupled virtual backup
finishes
And then perform a non-virtual incremental backup
VIRTUAL ENCAPSULATED
Specifies an encapsulated virtual backup
shell-command argument can be a string or variable
containing a string executed as part of the encapsulated
virtual backup
Shell commands execute a system-level backup of the IQ
Store as part of the backup operation
10 - 12
Archive Options
SIZE (KB)
Maximum size of the backed up data on that stripe
Should specify for unattended tape backups on platforms that
do not reliably detect the end-of-tape marker
Should also specify whether backup to disk is not the default
of 2GB
STACKER
Indicates that Sybase IQ is being backed up to an
automatically loaded multitape stacker device
Specify the number of tapes in that device
10 - 13
Simple Backup Examples
Example:
BACKUP DATABASE
INCREMENTAL
TO '/dev/rmt/0n' SIZE 15000
WITH COMMENT 'March 17 incremental backup of mydb
database'
Makes a full backup of the database to one tape device
Catalog Store is backed up first, then the IQ Store
Example using new syntax introduced in Sybase IQ 15 that shows a
backup of dbspace and dbfiles:
BACKUP DATABASE READONLY DBSPACES dsp2, dsp3
READONLY FILES dsp4_f1, dsp5_f2 TO bkp.RO
10 - 14
Take note that the keyword INCREMENTAL is not allowed with
READONLY FILES
Validating the Database
Should run sp_iqcheckdb before a backup to make sure the
database is in a usable state
Syntax:
sp_iqcheckdb 'mode target[ ][resources resourcepercent]'
Do not run sp_iqcheckdb while another user is doing inserts
and deletes
Results may be misleading. sp_iqcheckdb can falsely report
leaked blocks if there are multiple versions of any given table
You can pipe the output of sp_iqcheckdb to a file using #> in
your SQL statement
10 - 15
MODULE TOPICS
Backup
10 - 25
Restore
Diagnostic Stored Procedures
Utilities: dblog and db_backupheader
Restore Features
Allows parallel restore using multiple tape drives
Can restore full database
Can restore from incremental database backup
Can restore to a new location
10 - 26
Useful for a lost device
Before a Restore...
Must have Database Administrator privileges
Must have exclusive access
Set using the switch: -gd DBA
Must be connected to the utility_db database as DBA
No user can be connected to the database being restored
For a full restore, the Catalog Store and the Transaction Log
(.db and .log files) must not exist
For an incremental restore, the Catalog Store and the
Transaction Log must exist
For restoring a multiplex database, the server must be started
with the multiplex override switch, -iqmpx_ov 1
10 - 27
RESTORE DATABASE
Syntax:
RESTORE DATABASE 'database-name'
[ restore-option ... ]
FROM 'archive_device' ...
restore-option:
Version 15.0 only
READONLY dbspace-or-file [, ]
KEY key_spec
RENAME file-name TO new-file-path ...
database_name - relative or absolute path
key_spec
Quoted string representing the database encryption key
Includes mixed cases, numbers, letters, and special
characters
10 - 28
Continued
RESTORE DATABASE
RENAME option
Specifies a dbspace (in 12.7) or a dbfile (in 15.0) to be created
on a different path
Cannot rename the transaction log using this option
10 - 29
To move or rename the log file use the DBLOG utility
Examples
BACKUP statement and two possible RESTORE statements:
BACKUP DATABASE
READONLY DBSPACES iq_main TO '/system1/IQ15/IQ15_0/demo/backup/iqmain'
RESTORE statement 1:
RESTORE DATABASE 'iqdemo'
READONLY DBSPACES iq_main FROM '/system1/IQ15/IQ15_0/demo/backup/iqmain'
RESTORE statement 2:
RESTORE DATABASE 'iqdemo'
FROM '/system1/IQ15/IQ-15_0/demo/backup/iqmain'
10 - 30
Moving Database Files
Two Methods:
To move the database file that holds the Catalog Store (by
default, the DB file), specify the new name as
database_name
Example:
RESTORE DATABASE 'c:\\newdir\\mydbnew.db'
FROM 'C:\\iq\\backup1'
FROM 'C:\\iq\\backup2
For any other database file, use the RENAME option
10 - 31
See example on next slide
Continued
Moving Database Files
To move any database file other than the catalog store
Identify the files using sysfile
Back up the database
Restore the database using the rename option
select * from sysfile
file_id file_name
dbspace_name
store_type
---------------------------------------------------0
/sybase/salesdb/salesdb.db
SYSTEM
SA
16384
/dev/iqdisk/salesdb.iq
IQ_SYSTEM_MAIN
IQ
16385
/dev/iqdisk/salesdb.iqtmp
IQ_SYSTEM_TEMP
IQ
16386
salesdb.iqmsg
IQ_SYSTEM_MSG
IQ
10 - 32
Continued
Moving Database Files
Back up the database
backup database
to '/dev/rmt/0n' size 15000
Restore the database
restore database /sybase/salesdb/salesdb.db'
from '/dev/rmt/0n'
rename IQ_SYSTEM_MAIN
to '/dev/newiqdisk/salesdb.iq
rename IQ_SYSTEM_TEMP
to'/dev/newiqdisk/salesdb.iqtmp'
10 - 33
Selective Backup
Backs up either all READWRITE dbspaces or specific
READONLY dbspaces or dbfiles
Can take a READONLY selective backup and restore all
objects from this backup
Can take an all-inclusive backup and restore read-only files
and dbspaces selectively
Can take a READONLY selective backup of multiple read-only
files and dbspaces and restore a sub-set of real-only files and
dbspaces selectively
Restore of a read-only backup can happen only if that
dbspace or dbfile has not changed since the backup
10 - 34
Dealing with Restore Failures
Key point! Restore operation is not transactional
If restore fails mid-way, the files restored until that point may not
be in consistent state
If the files restored are inconsistent, the operation must be
repeated until it succeeds
When running backup operation, may specify option CRC ON to
enable restore time verification
After the backup operation finishes, as a practice, remove the
write permission on the backup archive to avoid accidental
overwrite
Before starting restore with Sybase IQ 15.0, may also run
command-line utility db_backupheader to verify backup archive
header information
10 - 35
Notes and Restrictions
Must restore full and incremental backups in the correct order
Sybase IQ does not rewind tapes before using them
On rewinding tape devices, each tape must be positioned
To ensure the database has been restored correctly, run
sp_iqcheckdb 'check database' after Restore
10 - 36
IQ Backup and Restore Performance
Multiple tape drives provide the best performance
Set CRC OFF in the BACKUP command to improve the
speed of backup and restore
Increase memory during backup
Adjust BLOCK FACTOR
Keep the size of the Catalog Store small
10 - 37
Creating non-IQ tables in it will increase backup and restore
time
Estimating Dump Size
Sybase IQ backs up
compressed data (no
zeros)
sp_iqstatus
Main IQ Blocks
Used
output gives size of
compressed data
Use
19089 * 4096 +
catalog store =
Total Bytes
10 - 38
MODULE TOPICS
Backup
10 - 39
Restore
Diagnostic Stored Procedures
Utilities: dblog and db_backupheader
sp_iqbackupsummary
Summarizes backup operations performed
DBA authority required
Syntax:
sp_iqbackupsummary [timestamp]
timestamp specifies the interval for which to report backup
operations
If a timestamp is specified, the procedure returns only those
records with bu_time greater than or equal to that time
If no timestamp is specified, the procedure returns all the
backup records in ISYSIQBACKUP
10 - 40
Summary Information Detail
Column descriptions:
10 - 41
Column name
Description
backup_id
Identifier for the backup transaction
backup_time
Time of the backup
backup_type
Type of backup: Full, Incremental since incremental, or
Incremental since full
selective_type
Subtype of backup: All, All RW files, or RO file
virtual_type
Type of virtual backup: Non-virtual, Decoupled, or
Encapsulated
depends_on_id
Identifier for transaction that the backup depends on
creator
Creator of the backup
backup_size
Size of the backup file in MB
user_comment
User comment
backup_command
The backup statement issued (minus the comment)
sp_iqbackupdetails
Shows all the dbfiles backed up in a particular backup
specified by its transaction identifier
DBA authority required
Syntax:
sp_iqbackupdetails [backup_id]
10 - 42
backup_id specifies the backup operation transaction identifier
Detail Information Retrieved
Column descriptions:
Column name
Description
backup_id
Identifier for the backup transaction.
backup_time
Time of the backup.
backup_type
Type of backup: Full, Incremental since incremental, or Incremental since full.
selective_type
Subtype of backup: All, All RW files, or RO file.
dbspace_name
Name of the dbspace from ISYSIQBACKUPHISTORYDETAIL. If it matches the
dbspace name in ISYSDBSPACE for a given dbspace_id. Otherwise null.
dbspace_rwstatus
ReadWrite or Read Only.
dbspace_size
Size of dbspace at time of backup in MB.
dbspace_backup_size
Size of data backed up in the dbspace.
dbfile_id
Identifier for the dbfile
dbfile_name
The logical file name if it was not renamed after the backup operation. If renamed,
null.
dbfile size in MB
Size of the dbfile, in MB
dbfile_backup_size
Size of the dbfile backup, in MB
dbfile_path
The dbfile path from ISYSBACKUPDETAIL, if it matches the physical file path
(file_name) in ISYSDBFILE for a given dbspace_id and the dbfile_id. Otherwise null.
10 - 43
sp_iqrestoreaction
Shows what restore actions are needed to bring database to
a consistent state with a given past date
Syntax:
sp_iqrestoreaction [ timestamp ]
Result Information:
When successful, suggests restore actions that will return the
database to a consistent state
Returns an error if database cannot be brought to a consistent
state for the timestamp
10 - 44
Try again after consulting sp_iqbackupsummary with a different
timestamp
MODULE TOPICS
Backup
10 - 45
Restore
Diagnostic Stored Procedures
Utilities: dblog and db_backupheader
dblog Utility
Utility program to manipulate the transaction log for your
database file (.log file)
The executable will be in the path
Syntax:
dblog [options] database-file
Use dblog to:
Create a new transaction log
Recommended after a successful restore as a matter of routine
Start or Stop a mirror for the transaction log
The database must run with a transaction log
10 - 46
Continued
dblog Utility
dblog Switches Options
-t log-name (Set the transaction log name)
-m mirror-name (Set transaction log mirror name)
-r (stop mirror for transaction log)
-o (log output messages to a file)
-q (Quiet mode do not print messages)
Example:
Start a new database transaction log for iqdemo
dblog t
10 - 47
demolog.log
iqdemo
db_backupheader
Utility used to read the backup archive file
It does not connect to the database
Syntax:
db_backupheader <first backup file>
The backup archive information includes:
Backup information
Database information at the time of the backup
dbspace information for each dbspace in the database
dbfile information for each dbfile in the dbspaces
Useful utility in the case you need to know every dbspace in
the database when using the RENAME option of RESTORE
10 - 48
Continued
db_backupheader
Partial sample:
IQ Backup Header Display of File: "iqdemo.bu.1"
Volumn Header: "VOL10000014"
Header 1: "HDR1000000000000000000000000000000000"
Header 2: "HDR2D000000000000"
Backup Data Record:
DB Name: "iqdemo.db"
DB Type: "ASIQ"
Current Backup ID: "28683"
Depends On ID: "0"
Last Full ID: "0"
Creation Date: "2009-06-16 00:59:00.000"
10 - 49
Continued
db_backupheader
Partial sample:
Creator: "DBA"
Total Tapes: "1"
Backup Type: "Full"
Media: "Othr"
Backup Version: "6"
Backup Method: "Arch"
Backup Location: "/home/sybase/iqdemo.bu"
Filler: 0x0
Backup Data Record Extended:
...
10 - 50
dbbackup Utility
Makes a copy of the IQ Transaction Log
Syntax:
dbbackup [options] target-directory
Can truncate the transaction log, freeing disk space and
improving recovery speed without having to stop and restart
your server
However, to back up an entire Sybase IQ database, always
use the BACKUP statement NOT dbbackup
10 - 51
Why? BACKUP backs up all database files, and is the only
way to back up the catalog store
dbbackup Options
10 - 52
Option
Description
@data
Read options from the specified environment variable or
configuration file
-c "keyword=value; ... "
Supply database connection parameters
-l (lowercase L) file
Live backup of the transaction log to a file
-q
Quiet mode do not print messages
-r
Copy the old transaction log to a new name and start a
new empty log
-xo filename
Truncate (delete and restart) the transaction log
Live Backup of Transaction Log
Must use option l (lowercase L)
Install and configure Sybase IQ on the secondary machine
Periodically, make a full backup to the secondary machine
Run a live backup of the transaction log to the secondary machine
using:
dbbackup -l path\filename.log c
"connection_string
Run the dbbackup utility from the secondary machine
10 - 53
If the primary machine becomes unusable, you can restart your database
using the secondary machine
Database file and the transaction log hold the information needed to
restart
Module Summary
During the course of this module, you have learned:
How to do develop a data backup and restore strategy
How to schedule routine backups
Mixing full and incremental backups
Restores should be tested on a periodic basis
How to use sp_iqcheckdb to validate the database before a
backup and after a restore
How to use Sybase IQ 15 Diagnostics stored procedures
How to use the new Sybase IQ 15 SYSIQBACKUPHISTORY
and SYSIQBACKUPHISTORYDETAIL to investigate backup
history
How to use dbbackup and the new Sybase IQ 15 Utility
db_backupheader
10 - 54
Continued
Module Summary
Methodology for using technology provided by some SAN
manufacturers to "mirror" a Sybase IQ database which can be
used in lieu of a full database backup
The shadow devices and files can be used to start an instance
of IQ to perform maintenance tasks such as sp_iqcheckdb
10 - 55
