SQL SERVER – Creating All New Database with Full Recovery Model

Sometimes, complex problems have very simple solutions. Let us see the following email which I received recently.

“Hi Pinal,

In our system when we create new database, by default, they are all created with the Simple Recovery Model. We have to manually change the recovery model after we create the database. We used the following simple T-SQL code: CREATE DATABASE dbname.

We are very frustrated with this situation. We want all our databases to have the Full Recovery Model option by default. We are considering the following methods; please suggest the most efficient one among them.

1) Creating a Policy; when it is violated, the database model can be fixed
2) Triggers at Server Level
3) Automated Job which goes through all the databases and checks their recovery model; if the DBA has not changed the model, then the job will list the Databases and change their recovery model

Also, we have a situation where we need a database in the Simple Recovery Model as well – how to white list them?

Please suggest the best method.”

Indeed, an interesting email! The answer to their question, i.e., which is the best method to fit their needs (white list, default, etc)? It will be NONE of the above.

Here is the solution in one line and also the easiest way:

Just go to your Model database: Path in SSMS >> Databases > System Databases >> model >> Right Click Properties >> Options >> Recovery Model ‑ Select Full from dropdown.

Every newly created database takes its base template from the Model Database. If you create a custom SP in the Model Database, when you create a new database, it will automatically exist in that database. Any database that was already created before making changes in the Model Database will not be affected at all.

Creating Policy is also a good method, and I will blog about this in a separate blog post, but looking at current specifications of the reader, I think the Model Database should be modified to have a Full Recovery Option.

While writing this blog post, I remembered my another blog post where the model database log file was growing drastically even though there were no transactions SQL SERVER – Log File Growing for Model Database – model Database Log File Grew Too Big.

NOTE: Please do not touch the Model Database unnecessary. It is a strict “No.” If you want to create an object that you need in all the databases, then instead of creating it in model database, I suggest that you create a new database called maintenance and create the object there.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

9 thoughts on “SQL SERVER – Creating All New Database with Full Recovery Model

    • Hi Gaurang,

      That is what the DBA were doing but it is after creating the database. The requirement is when the database was created it has to be like that from the beginning.

      Many thanks,

      Like

      • Yes,that is True.And We can not Create with Database with Full recovery mode via T-SQL.
        1. we have to create database via T-SQL.
        and Then
        2. We have to use Alter statement to set it Full Recovery model if new Database is not in Full Recover Mode.

        And that is True that one has to modify Model Database.
        new Database apply recovery model that model database is using.

        Like

          • Hi pinal,
            Here i have two questions..

            As i know, While we create a primary key on a table its automatically creates a cluster index.. and it uniquely identifies rows,,, then

            1. is it possible to create a one more cluster index, if not why..?

            2.I just want to know how the sql server2005 resolves dead lock it self..?

            Like

  1. Be very careful with the model database, since the tempdb, while rebuilt at every instance startup, will also inherit settings of the model database (collation, for example).
    It is important to back up the model database after introducing changes to it.

    Like

  2. It only allow to have 1 cluster index as the sort order of table will be reorder base on the cluster index. hence, u can only have 1 cluster index. SQL itself will choose the deadlock victim and kill it , then roll back the transaction.

    Like

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #031 | Journey to SQL Authority with Pinal Dave

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s