?
Solved

SQL Server and default recovery model

Posted on 2011-10-17
9
Medium Priority
?
435 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:tommyeriksen
9 Comments
 
LVL 11

Expert Comment

by:Simone B
ID: 36981302
Have you done a reboot since making that change, or at least restarted SQL Server?
0
 
LVL 1

Author Comment

by:tommyeriksen
ID: 36982967
yes I did...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36983880
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 10

Expert Comment

by:Ramesh Babu Vavilla
ID: 36985044
by default all the user defined database are created in FULL recovery mode
,restart you sql services every thing will be fine
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36985595
>>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
 
LVL 1

Author Comment

by:tommyeriksen
ID: 36985618
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36985649
>>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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 36985674
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
 
LVL 1

Author Closing Comment

by:tommyeriksen
ID: 36987986
By inspecting the SQL Error logs I successfully found the cause of the problem. Thanx.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Integration Management Part 2
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question