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.

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.

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.

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.

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.

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.

Pinal Dave is a SQL Server Performance Tuning Expert and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and has written over 4000 articles on the database technology on his blog at a https://blog.sqlauthority.com. Along with 16+ years of hands on experience he holds a Masters of Science degree and a number of database certifications.