You can use a database other than model as the template for an in-memory database to load reference data (for example, tables and stored procedures) into the database created with the template. The template database must be an existing disk-resident user database with full durability. Using templates to create a database and minimally logged DML are supported only on low durability databases.
You cannot use templates to create relaxed-durability databases that use a durabilty of at_shutdown. You can use templates only for databases that have a durability of no_recovery.
Use the create database use database_name as template command to specify a database other than model as a template for any database that has been created with the no_recovery parameter.
Once you create a database that uses a database other than model as its template, Adaptive Server re-creates the dependent database, using data from the template database, when the server restarts.
When you restart Adaptive Server, template databases are recovered before databases that use them as templates. If Adaptive Server cannot recover a template database, databases that depend on that template database cannot be re-created.
Adaptive Server applies the attributes of the template database when you create the dependent database. Attributes you specify as part of the create database command override the template database’s attributes. Database options and attributes for databases you create using the template persist when Adaptive Server restarts, and when Adaptive Server re-creates the database from its template. Any changes you make to attributes of the template database are not used when Adaptive Server re-creates the dependent database during subsequent restarts.
You cannot drop a database if any databases uses it as a template. You must first drop all databases that use this database as a template, or use alter database to detach the template database from all its dependent databases.
User-defined segments in template database may not function
as expected after they are copied from the template database into
an in-memory or relaxed-durability database. Segments direct space
allocation onto specific database devices (or device fragments) by
mapping the syssegments table to the sysuages table
in the master database. Template databases may use a different mapping
than the in-memory or relaxed-durability databases for which they
are providing a template since the device layout for these databases may
use a different number of devices and differently sized fragments. You must
carefully plan and define the template database’s user-defined
segments before using these segments for in-memory or relaxed-durability
databases.
Run sp_helpdb to report information about templates for:
User databases – determine if, and for which databases, the user database has been used as a template. This section of sp_helpdb output shows that the pubs2 database was used as a template for the pubs3 and pubs5 in-memory databases:
template_for -------------------------------------------------- pubs3 pubs5
Databases created from a template – determine which database was used as a template during creation (if it was not model). This section of sp_helpdb output shows that you used the pubs2 database as the template for the pubs3 database:
template -------------------------------------------------- pubs2
