Using the model database
As a database
administrator, you probably get requests to re-create sets of standard objects
within new databases that you build for development or testing purposes. While
this is a fairly easy task, it is probably not one of your favorite things to
do. Consider using the model database to help you accomplish this task.
Model is a
system database delivered with SQL Server and is used as a template for other
databases. When you issue the CREATE DATABASE command, SQL Server reads the
model database and creates the new database in its own image. Given this fact,
you can create most any object you need in the model database, and it will be
carried over to the newly created database.
This can be
quite helpful if you have user-defined datatypes, predefined users, or
administration procedures that occur on a system- or server-wide basis.
Another note
regarding the model database is that the tempdb database is re-created on SQL
startup. Like all other databases, tempdb is based on model, so model must be
present and free from corruption in order for tempdb to be deployed. SQL Server
must be able to obtain an exclusive lock on model before any database is
created. This means that no user is allowed to access the model database while
the CREATE DATABASE command is processed.