Model Database in SQL Server

Model database is another important system database in SQL Server. It is also called as “Template DB”; SQL Server uses this database as a template db to create a new database. We need this database for SQL Server to come online, because this database is being used to create TempDB while restarting the service/server.

At times, few developers will place objects (tables/procedures etc.) that needs to be created for every newly created database. This would avoid the work of creating those every time.

Recovery Model

Recovery model of “Model” database is depending on the SQL editions. The editions mostly suitable for test and development comes up with recovery model “SIMPLE” and higher editions with “FULL”. This is mainly because Production environment databases require a point in time recovery options on the verge of going corrupted.

It is also a good idea to have a full back up of model database in a daily basis considering there would not be any user transactional databases on Model database.

Model will act as a template only for new database creation, it does not have any impact on restore databases.

Its a short topic, but the importance of Model is high for SQL Server!

If you enjoyed this blog post, feel free to share it with your friends!

One thought on “Model Database in SQL Server”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s