Issuing a Typical Archive Database Command Sequence

Generally, creating an archive database includes creating a scratch database, creating the archive database, loading the data, bringing the database online, and loading the transaction log.

  1. Create the scratch database, if necessary. To create a 150MB traditional database called scratchdb, for example, issue:
    create database scratchdb
          on datadev1 = 100
          log on logdev1 = 50
  2. Designate the database you just created as a scratch database:
    sp_dboption "scratchdb", "scratch database", "true"
  3. Create the archive database. This creates an archive database called archivedb, with a 20MB modified pages section:
    create archive database archivedb
          on datadev2 = 20 
          with scratch_database = scratchdb
  4. Materialize your archive database using load 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:
    dbcc checkdb(archivedb)
  7. Load a transaction log dump using load tran and restore objects from the archive database using select into or bcp:
    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