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.

SQL SERVER - Creating All New Database with Full Recovery Model modelmodification

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 (https://blog.sqlauthority.com)

Previous Post
SQLAuthority News – Best SQLAuthority Posts of May
Next Post
SQL SERVER – Puzzle – Statistics are not Updated but are Created Once

Related Posts

No results found.

8 Comments. Leave new

  • Imran Mohammed
    June 1, 2011 7:16 am

    I Wonder, Who is the DBA of that company….

    ~ IM.

    Reply
  • Gaurang Patel
    June 1, 2011 11:45 am

    How About via TSQL Command?

    ALTER DATABASE [DBName] SET RECOVERY FULL

    Reply
    • 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,

      Reply
      • Gaurang Patel
        June 1, 2011 12:57 pm

        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.

      • That is exactly my point!

      • 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..?

  • Feodor Georgiev
    June 1, 2011 3:29 pm

    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.

    Reply
  • 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.

    Reply

Leave a Reply

Menu