Archive databases are used to access data from a backup file directly without having the restore the database. Let’s say you lost some data in a table but had many other changes to other tables since the last backup. Just loading the last backup is not an option since you’d lose everything since the last backup. Of course, if you work with transaction log dumps, you can reduce the loss of data but very often it’s still too much. Additionally, in some cases you know the data you want to reload have not changed since the last backup (i.e. some kind of master data). So the best solution would be to be able to keep the current database but just reload this one table. Or maybe you do not want to reload a complete table but just copy a few deleted lines back in a table.
That’s exactly what an archive database is for. You cannot dump an archive database. An archive database is just a normal database dump loaded in a special way so that you can access the data without having to do a regular load of the dump which would overwrite everything.
So what do you need in order to mount a database as an archive database. Well, you need two additional databases:
- A “scratch database”
- An archive database
The “scratch database” is a small database you need to store a system table called sysaltusages. This table maps the database dump files you are loading to the archive database.
The archive database is an additional database you need to store “modified pages”. Modified pages are pages which are created additionally to the pages stored in the dump files. These are e.g. the result of a recovery performed after loading the database dump. So this database is typically much smaller than the dump files you are loading. But it is difficult to tell upfront how big it will be.
So once you have loaded an archive database, the data you see come from these three sources:
- The loaded dump files
- The scratch database
- The archive database
So let’s first create the two database (I assume here you have some devices available to create these databases).
First we need to create the scratch database:
use master go create database scratchdb on scratch_data_dev='100M' log on scratch_log_dev='100M' go
This will create the scratch database and take in online. Then we need to mark this database as a scratch database:
sp_dboption 'scratch', 'scratch database', 'true' go use scratch go checkpoint go
Then we need to create the archive database:
use master go create archive database archivedb on archive_data_dev='100M' with scratch_database = scratchdb go
Replace scratch_data_dev, scratch_log_dev and archive_data_dev by the names of the devices you want to create the data and log fragments of the scratch database and the data fragment of the archive database.
Now we’re ready to load the dump. Just do it the way you would load the database to restore it but only load it to the just created archive database e.g.:
load database archivedb from '...'
You can of course also load the transaction logs with:
load transaction archivedb from '...'
Note that while loading the database dump or the transaction log dumps, you might get error message saying that either the transaction log of the scratch database or the modified pages section of the archive database run full e.g.:
There is no more space in the modified pages section for the archive database ‘pdir_archive_db’. Use the ALTER DATABASE command to increase the amount of space available to the database.
Depending on the message you get, you’ll have to add more space for the transaction log of the scratch database or extend the archive database using alter database. Note that ASE usually gives you a chance to do it before aborting. But at some point in time, it will probably abort, so do not take your time 😉
If you do not care about the recovery and have limited storage available for the archive database you can use:
load database archivedb from '...' with norecovery
Loading with norecovery also reduces the time required to load. Also the database is automatically brought online (this also means you cannot load additional transaction logs). The downside is that the database might be inconsistent (from a physical and transactional point of view).
If you did not use the norecovery option, you have to bring the archive database online:
online database archivedb
Once you are done with the archive database and do not need it anymore, you can just drop both databases:
drop database archivedb drop database scratchdb