SQL Server and default recovery model

I have a cutomer with a server with MS Sql 2005. The company is an accounting-firm, and the accounting software creates a new sql database for every company which the account-firm do the accounting... And for some reason the recovery model on all newly created databases are set to "Simple", but because of their backup-solution we have to change the recovery model to "Full". But we want to configure so that every database is created with the recovery model "Full" by default. I read somewhere that if we changed the model-database, and set recovery model to "Full", it would create all future databases with the same recovery model. But in my case it doesn't for some reason. Can anyone help me?
LVL 1
tommyeriksenAsked:
Who is Participating?
 
Anthony PerkinsCommented:
In case that was not clear what you should see in the SQL Errorlog is:
1. When the database is created:
Starting up database 'YourDatabaseNameGoesHere'.
A lot of database settings ("Setting database option") including the Recovery Model (all these settings should match the model database)

2. If the Recovery Model is changed later to Simple you will see the following:
Setting database option RECOVERY to SIMPLE for database YourDatabaseNameGoesHere.

How can you tell them apart?  Look at the time it occurred.
0
 
Simone BSenior E-Commerce AnalystCommented:
Have you done a reboot since making that change, or at least restarted SQL Server?
0
 
tommyeriksenAuthor Commented:
yes I did...
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Anthony PerkinsCommented:
It will use the Recovery Model in the model database, however there is nothing stopping anyone from stting it to Simple after the fact.  Fortunately you can easily verify this by inspecting the SQL Server Error Log after a new database is created.
0
 
Ramesh Babu VavillaCommented:
by default all the user defined database are created in FULL recovery mode
,restart you sql services every thing will be fine
0
 
Anthony PerkinsCommented:
>>by default all the user defined database are created in FULL recovery mode<<
No, that is not true.  By default a new database has the same Recovery Model a the model database.

>>,restart you sql services every thing will be fine<<
Pray tell, why in the world do they have to do that.  While you are at it, why don't you suggest they reinstall SQL Server!
 
0
 
tommyeriksenAuthor Commented:
Thank you for all feedback, I'll check the SQL Server logs to see if I can find a reason why the new databases are created as "Simple"
0
 
Anthony PerkinsCommented:
>>I'll check the SQL Server logs to see if I can find a reason why the new databases are created as "Simple" <<
You are missing the point.  If the model database is in fact in Full Recovery Model as you state than the database will be created in Full Recovery Model.  What you need to look for is where it is changed to Simple.  The entry will look like this:
Setting database option RECOVERY to SIMPLE for database YourDatabaseNameGoesHere.
0
 
tommyeriksenAuthor Commented:
By inspecting the SQL Error logs I successfully found the cause of the problem. Thanx.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.