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
4) SEE THE SURPRISE
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 :)
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
4) SEE THE SURPRISE
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 :)
Comments
Tell me if I am Wrong ..