Archive Databases in Sybase ASE

Archive Databases in Sybase ASE (15.0.2 +) - Quick Overview

NOW ACCESS DATA DIRECTLY FROM BACKUP FILE WITHOUT RESTORING IT !!!!

Archive Database is a type of database in ASE which is built primarily on database backup files and partly on server devices.  A system table, sysaltusages, maps these database backup files to archive database. This system table is stored on a user defined database which should be designated as "scratch database"

Server devices  (like the one used by normal databases, initialized by disk init) on Archive databases are used to store pages which are result of recovery operation performed while loading archive database with database dump.  When an archive DB is loaded, a logical mapping is created between pages in database dump file and archive database. In light of this, the size of your archive database will be very less as compared to size of source database.

Figure below shows a high level view of Archive Database.

Add caption

Possible applications of Archive Databases -

    Before loading a dump file to a database, in order to ensure that there is no corruption, an archive database can first be created on it and consistency checks could be run. Creating an archive database is very fast as only logical mappings are to be created.

    Archive Databases can be used for Object level Recovery

Limitations

Read Only Databases (Although dbcc with fix can be applied on it)

Database dumps from files/disks can be only used, tapes are not supported

Compressed dumps should be created using "with compression" and not "compression::"

Implementation Steps -

    1.Either create a new database or designate an existing databases as scratch db.
--create DB
Create database scratchdb on data_dev = 100 log on log_dev = 50
--mark it as acratch db
sp_dboption scratchdb, 'scratch database', true
2. Create an archive database
create archive database archive_db on data_dev1 = 20 with scratch_database = scratchdb
3. If you are going to load a compressed dump, set a compression memory pool first using below command -
sp_configure 'compression memory size' , 2560
4. Load archive database with dump of desired database -
load database archive_db from '/dev/ddumpd/prod_db_01'
stripe on from '/dev/ddumpd/prod_db_02'

Bring it online
Online database archive_db
5.Use load database with norecovery, This give you immediate access to the dump files as recovery will not happen. You don't need to execute online database either.

Ardent DBA