Dumps and Loads

DUMPS & LOADS

• A database backup or DUMP is a physical backup of an entire database
• A transaction log backup or DUMP is a physical backup of a database transaction log

Backup needs

Adaptive server automatic recovery procedures protect you from power outages and computer failures

other issues to be considered

• Media failures
• Machine Failures
• Site Failures
• Corrupted Data
• Application Error

*** Automatic recovery fails due to corruption or internal inconsistency

Additional recovery methods

Mirroring : A method that results in an up-to-the-minute copy of the data

Warm Standby : A method that results in a relatively up-to-datecopy

** Mirroring → up to the minute

** Warm standby → up to datecopy

Ensuring Database Consistency

Sybase recommends ensuring database consistency before dumping the database

we can check the database consistency following

• dbcc checkstorage (no need to keep in single user mode)
• dbcc checkdb (Single user mode required)
• dbcc checkalloc (Single user mode required)
• dbcc checkcatalog (Single user mode required)

** If this is too time consuming ,run dbcc checktable and dbcc tablealloc for active tables without using the “ Single user mode ”

** dbcc checkstorage does the most complete checking with the least impact on performance

Backup types :

Database backup:both a database and its transaction log are backed up

Transaction log backup: only transaction log is backed up

up-to the-minute recovery:( Mirroring ) :: Transaction log is backed up without truncation even if the disk containing the data is inaccessible

• also known as “ Recovering the log ”
• the log and data must be on separate physical disk

Backup media:

Tapes : creates offline library of database and log dumps

Disks : Generally not recommended as dump device system or disk crashes may block dump recovery

CD-Rom: Reliable,tapes can degrade ,need high speed burners

Disk to tape or CD-Rom:

Dump a database to a file on disk then use an Operating system or third party utillity to backup that up to tape or cd

• Both the source and destination database should be of the same size
• All ASE backups are performed by the backup server
• Dump command includes database | transaction logs using backup server
• Backup server should be running while performing dumps and loads

Syntax: dump database <dbname> to <”devicename”>

Example: dump database pubs2 to “/opt/sybase/dump/pubs2.dat”

Loads: The Load restores the database and /or log from dump device

• The destination database where we are going to load should be sync up with the source database
• While loading the database there should be no user's to be connected to a database
• Database should in a “ single user mode ”
• After the load is done the database should be brought online

Syntax: online database <databasename>

Syntax :load database < dbname >from < devicename >

Example :

Phases of Dumps:

1. DBPAGES
2. FLUSHPAGES
3. SCANLOGPAGES

Phase 1

• Phase 1 is the DBPAGES phase
• The Backup Server scans and dumps all the allocated pages (both data and log pages) for the given database, without checking to see whether any of the pages were updated while this phase is going on.

Phase 2

• Phase 2 is the FLUSHPAGES phase

• All the data pages that were modified during but not logged in phase 1 (for example, a fast bcp) are dumped.
• The dump instant is the end of this phase. Recovery restores the database to the state after phase 2.

Phase 3

• Phase 3 is the SCANLOGPAGES phase
• The log pages are dumped again to capture up-to-the-minute changes.
• dump transaction has only one phase: the SCANLOGPAGES phase.

Backup Servers

All ASE backup servers are performed by backup server

• Creates backup while users are active
• Backup server should be running while performing dumps and loads
• Dumps one database across many devices (Stripes)
• Executes automatic dumps
• Dumps do not have backward compatibility

Stripes

The max no of stripes that backup server can use is limited by the maximum number of open server threads it can create

Open server imposes a maximum limit of 12k on the number of threads an application can create .

Back server creates one service thread for each stripe .There for the max number of local stripes backup server can dump to or load from is 12,286

*** Different compressions levels can be specified with each stripe

Dump with compression

Database and Log dumps can be compressed levels of compression is 0 to 9

0 → No Compression

9 → Max Compression

Syntax for DUMPS

Syntax : Dump database <dbname> to “ device name ”

Example : Dump database pubs2 to “opt/sybase/dump/pubs2.dat ”

Syntax : dump database <dbname> to “compress:: level/.../.../.../.cmp”

stripe on “compress:: level/.../.../.cmp2”

stripe on “compress:: level/.../.../.cmp3”

Example : dump database pubs2 to “compress::0::/opt/sybase/dump/pubs2.cmp”

stripe on “compress::0::/opt/sybase/dump/pubs2.cmp2”

stripe on “compress::0::/opt/sybase/dump/pubs2.cmp3”

Syntax for LOAD:

Syntax : Load database <dbname> to “compress:: level/.../.../.../.cmp”

stripe on “compress:: level/.../.../.cmp2”

stripe on “compress:: level/.../.../.cmp3”

Example : Load database pubs2 from “compress::0::/opt/sybase/dump/pubs2.cmp”

stripe on “compress::0::/opt/sybase/dump/pubs2.cmp2”

stripe on “compress::0::/opt/sybase/dump/pubs2.cmp3”