Things to consider while changing the Recovery model

Hi Friends,

This is not a great post for sure. However people like me would fall in to  trap which after realizing  makes me look real stupid.

I would like to share an incident that happened today. We are driving Migration of Databases as part of it I was supposed to migrate it from SQL 2008 R2 to SQL 2014 Version.

After restoring the database I need to involve that database to Always ON as well.

And I would like to share funny thing with this as well that I had in the past. We had a database of size 2 TB which was running on older version and I took a backup of it & restored it on to higher version.

To my surprise the database was in simple recovery model which I didn't notice. BOOM a serious miss. So this time I checked it prior however I made a mistake even this time.

This is how it went.

1) I made the database to read-only ensuring there shouldn't be any DML operations.

2)Changed the recovery model to full

3)Taken Full Backup

4)Now I am trying to take log backup & it was not happening.

What?? I checked the recovery model & it says FULL then why????

when we make the database read-only it didn't change the recovery model to full even though it is reflecting inside sys.sysdatabases and in the below query as well.

select DATABASEPROPERTYEX('test2','recovery')

Error Message:
Msg 4214, Level 16, State 1, Line 21
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 21
BACKUP LOG is terminating abnormally.

And I will share one more scenario with you.

1) Database is in simple recovery model we changed it to FULL

2) you thought not to disturb any of your differential backup strategies so you went with taking copy only full backup

3) Then you thought of doing log backup as DB is in Full recovery model. Even if you check recovery model it will show you as FULL


I left to readers to comment on the above 2 scenarios as it is simple to answer.

My only question in the above scenarios is even though I took the full backup & the recovery model being FULL why I m not able to take Log backup???

As always it's Fun Sharing :)


bikram said…
Boss, Good Morning...Thanks a lot for the Posts that you are sharing for knowledge..Lets Go to your Question..I believe we can't take Copy_only backup as a base backup to go with..

Tell me if I am Wrong ..
Of course that is true because the database is acting in Pseudo Simple Recovery Model. But I will try to dig the internals to see what is making copy only to differ from Normal Backup