August 10, 2017 at 6:44 pm
Good Morning Experts,
We took a full backup of database which was in simple recovery mode. We changed recovery mode to FULL and then took a log backup , but it failed. We took another full backup and then took a log backup and it completed successfully. Could you please help me understand the reason or internals behind this.
August 10, 2017 at 7:27 pm
coolchaitu - Thursday, August 10, 2017 6:44 PMGood Morning Experts,
We took a full backup of database which was in simple recovery mode. We changed recovery mode to FULL and then took a log backup , but it failed. We took another full backup and then took a log backup and it completed successfully. Could you please help me understand the reason or internals behind this.
Taking a log backup requires that a full backup has been taken. After you switch the recovery model to full, you have to take a full backup before taking a log backup. The database was still acting like it was in simple and the log was still auto-truncating. Taking a full backup resets the log chain.
August 10, 2017 at 10:42 pm
Ed Wagner - Thursday, August 10, 2017 7:27 PMcoolchaitu - Thursday, August 10, 2017 6:44 PMGood Morning Experts,
We took a full backup of database which was in simple recovery mode. We changed recovery mode to FULL and then took a log backup , but it failed. We took another full backup and then took a log backup and it completed successfully. Could you please help me understand the reason or internals behind this.
Taking a log backup requires that a full backup has been taken. After you switch the recovery model to full, you have to take a full backup before taking a log backup. The database was still acting like it was in simple and the log was still auto-truncating. Taking a full backup resets the log chain.
As per my understanding, when database is in simple recovery mode, it truncates the log on checkpoint. Is this the reason why we cannot take log backups in simple recovery mode
August 11, 2017 at 1:10 am
coolchaitu - Thursday, August 10, 2017 10:42 PMIs this the reason why we cannot take log backups in simple recovery mode
Yes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2017 at 2:11 am
GilaMonster - Friday, August 11, 2017 1:10 AMcoolchaitu - Thursday, August 10, 2017 10:42 PMIs this the reason why we cannot take log backups in simple recovery modeYes.
Thanks Gail. Does checkpoint truncate both committed and uncommitted transactions?
August 11, 2017 at 3:30 am
I answered that in your other thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 16, 2017 at 8:22 am
coolchaitu - Thursday, August 10, 2017 6:44 PMGood Morning Experts,
We took a full backup of database which was in simple recovery mode. We changed recovery mode to FULL and then took a log backup , but it failed. We took another full backup and then took a log backup and it completed successfully. Could you please help me understand the reason or internals behind this.
The transactional log backup can be taken only if a full backup has been done while it was (, and it is) in Full Recovery mode (irrespective of previous modes) where-in the logs won't get auto truncated. You couldn't take a trans log backup after changing the mode as the log chain wasn't reset ; the same being the reason.
August 16, 2017 at 8:38 am
Arsh - Wednesday, August 16, 2017 8:22 AMcoolchaitu - Thursday, August 10, 2017 6:44 PMGood Morning Experts,
We took a full backup of database which was in simple recovery mode. We changed recovery mode to FULL and then took a log backup , but it failed. We took another full backup and then took a log backup and it completed successfully. Could you please help me understand the reason or internals behind this.
The transactional log backup can be taken only if a full backup has been done while it was (, and it is) in Full Recovery mode (irrespective of previous modes) where-in the logs won't get auto truncated. You couldn't take a trans log backup after changing the mode as the log chain wasn't reset ; the same being the reason.
Not quite. What you say is true if the previous mode was Simple Recovery only, and only a switch to Simple will prevent log backups from being taken after switching.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 16, 2017 at 10:52 am
True Gail.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply