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