create archive database

Creates an archive database.

Syntax

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

Parameters

encrypt with key_name – creates an archive database that is encrypted, using the same key you used to encrypt the database you backed up (dumped).
on – specifies the modified pages section. The SAP ASE 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.
size – 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.

Examples

Example 1 – This could be a typical archive database command sequence.

Create the scratch database, if necessary:
```
create database scratchdb
    on datadev1 = 100
    log on logdev1 = 50
```
This creates a 150MB traditional database called scratchdb.
Designate the database you just created as a scratch database:
```
sp_dboption "scratchdb", "scratch database", "true"
```
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.

Materialize your archive database:

load database archivedb
    from "/dev/dumps/050615/proddb_01.dmp"
    stripe on "/dev/dumps/050615/proddb_02.dmp"

Bring the database online:
```
online database archivedb
```
Check the consistency of the archive database using dbcc commands. For example:
```
dbcc checkdb(archivedb)
```

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

Usage

You can load dumps of the master database into an archive database.
You cannot use an in-menory database as an archive database.You should not use an in-memory database as a scratch database.

Permissions

There is no special permission to use the encrypt with option of the create archive database command. However, users need select permission on the database encryption key to be able to reference it as the key_name.

The permission checks for create archive database differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must have the `create database` privilege.
Disabled	With granular permissions disabled, you must be the system administrator or have `create database` privilege.