create archive database


Creates an archive database.


create archive database db_name
	[on db_device [= size]
		[, db_device [= size] ] ... ] 
	with scratch_database = db_name



specifies the modified pages section. Adaptive Server requires traditional database storage to store modified pages. Use the on clause to specify the location and size of the modified pages section.

db device

specifies the database device on which you want to create your modified pages section.


specifies the size of the modified pages section you want to create. If you omit size, 5120 pages are allocated.

with scratch_database

(required if a scratch database does not already exist) specifies the name of an existing database in which information about the archive database is maintained. The sysaltusages system table, which maps logical pages in the archive database onto physical pages, is stored in the scratch database.


Example 1

This could be a typical archive database command sequence.

  1. Create the scratch database, if necessary:

    create database scratchdb
        on datadev1 = 100
        log on logdev1 = 50

    This creates a 150MB traditional database called scratchdb.

  2. Designate the database you just created as a scratch database:

    sp_dboption "scratchdb", "scratch database", "true"
  3. Create the archive database:

    create archive database archivedb
        on datadev2 = 20 
        with scratch_database = scratchdb

    This creates an archive database called archivedb, with a 20MB modified pages section.

  4. Materialize your archive database:

    load database archivedb
        from "/dev/dumps/050615/proddb_01.dmp"
        stripe on "/dev/dumps/050615/proddb_02.dmp"
  5. Bring the database online:

    online database archivedb
  6. Check the consistency of the archive database using dbcc commands. For example:

    dbcc checkdb(archivedb)
  7. Load a transaction log dump, and restore objects from the archive database:

    load tran archivedb
        from "/dev/dumps/050615/proddb1_log_01.dmp"
    load tran archivedb
        from "/dev/dumps/050615/proddb1_log_02.dmp"
    online database archivedb
    select * into proddb.dbo.orders from     archivedb.dbo.orders
    load tran archivedb
        from "/dev/dumps/050615/proddb1_log_03.dmp"
    online database archivedb


You cannot use an in-menory database as an archive database.Sybase reommends that you do not use an in-memory database as a scratch database.



create archive database permission defaults to system administrators, who can transfer it to users listed in the sysusers table of the master database. However, create archive database permission is often centralized to maintain control over database storage allocation.

create archive database permission is not included in the grant all command.

See also