page contents

About the Post

Author Information

SQL Server’s Recovery Model Setting Based Upon Model (System) Database!

In my last article, I talked about COPY_Only backups and I very briefly mentioned Recovery Models for SQL Server. I will one do do a more detailed post when I find some time, but I did want to share this little tip with you about the impact the Model database can have on your databases.

This week I was setting up some database mirroring for a few environments using some setup scripts. It was failing and at first, I was puzzled by the result. I checked the script and I could see the databases get backed up, then get copied over to the secondary server to get properly restored. However, it would never get mirrored and I knew on most of other servers how we were configured, so I made a an oversight. As I was talking to myself trying to figure out what was happening, my co-worker asked me what was troubling me. I showed him the process I was doing  and he was also surprised that it would not mirror.

Then I was talking with Argenis Fernandez (Blog | Twitter) over Lync who reminded me of the impact the Model (system) database can cause. So let me show the impact 🙂

Lets check the Model database, which the default setting for all system databases is simple recovery model as you can see here:

As you might be aware, this setting will prevent you from doing any SQL high availability options like log shipping or database mirroring, plus you will lose the ability to restore to a specific point in time. So what happens if we create a database with the Model database setup like this one?

As you can see we created a new database called testwith_simple so lets check the properties:

As you can see it took the same setting as the Model database. So what happens if we change Model’s database on the fly without any restarts!

Now that we set the Model database to Full, let’s create a new database:

As you can see we created a new database called testwith_full so lets check the properties:

As you can see it took the same setting as the Model database again, which is the Full Recovery model. As usual, I hope this article helps.

Tags: , , , ,

Comments are closed.

Copy Protected by Chetan's WP-Copyprotect.