July 12, 2017 at 7:01 am
Good Morning Experts,
In Alwayson Availability Groups, why cant we take regular full backup on secondary replica?
July 12, 2017 at 7:14 am
Regular full backups mark the database and secondary databases are read only.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 12, 2017 at 7:21 am
Grant Fritchey - Wednesday, July 12, 2017 7:14 AMRegular full backups mark the database and secondary databases are read only.
I did not understand your reply Grant. Could you please explain
July 12, 2017 at 7:38 am
sql_lock - Wednesday, July 12, 2017 7:13 AMYou can take COPY_ONLY backup as this does not break the log chain.
Wrong. Full backups, normal full backups do not ever break the log chain.
Copy_only full backups don't reset the differential base, that's the difference between copy_only and normal.
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
July 12, 2017 at 7:39 am
coolchaitu - Wednesday, July 12, 2017 7:21 AMGrant Fritchey - Wednesday, July 12, 2017 7:14 AMRegular full backups mark the database and secondary databases are read only.I did not understand your reply Grant. Could you please explain
Regular full backups change the database. The secondary is read only, and hence cannot be changed. Hence only copy_only (which don't change the database) can run.
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
July 12, 2017 at 8:07 am
GilaMonster - Wednesday, July 12, 2017 7:38 AMsql_lock - Wednesday, July 12, 2017 7:13 AMYou can take COPY_ONLY backup as this does not break the log chain.Wrong. Full backups, normal full backups do not ever break the log chain.
Copy_only full backups don't reset the differential base, that's the difference between copy_only and normal.
"copy-only backups do not impact the log chain" is what I was referring to.
July 12, 2017 at 8:17 am
GilaMonster - Wednesday, July 12, 2017 7:39 AMcoolchaitu - Wednesday, July 12, 2017 7:21 AMGrant Fritchey - Wednesday, July 12, 2017 7:14 AMRegular full backups mark the database and secondary databases are read only.I did not understand your reply Grant. Could you please explain
Regular full backups change the database. The secondary is read only, and hence cannot be changed. Hence only copy_only (which don't change the database) can run.
Thanks for replying Gail. So we cannot take full backup of read-only databases as it resets the differential base. However, I came across this link https://technet.microsoft.com/en-us/library/ms190374(v=sql.105).aspx , which says we can take full backup of read-only databases. I am confused. Could you please clarify
July 12, 2017 at 8:27 am
coolchaitu - Wednesday, July 12, 2017 8:17 AMThanks for replying Gail. So we cannot take full backup of read-only databases as it resets the differential base.
No. We cannot take full backups of readable secondaries, as it would require changing a database that cannot be modified (because it's in an AG)
However, I came across this link https://technet.microsoft.com/en-us/library/ms190374(v=sql.105).aspxhttps://technet.microsoft.com/en-us/library/ms190374(v=sql.105).aspx , which says we can take full backup of read-only databases. I am confused. Could you please clarify
A read-only database cannot be modified. Therefore there cannot be anything changing the extents in teh database and setting bits in the differential bitmap, therefore the differential base doesn't need to be reset, as nothing can have modified the database since it went read-only
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
July 12, 2017 at 8:32 am
sql_lock - Wednesday, July 12, 2017 8:07 AMGilaMonster - Wednesday, July 12, 2017 7:38 AMsql_lock - Wednesday, July 12, 2017 7:13 AMYou can take COPY_ONLY backup as this does not break the log chain.Wrong. Full backups, normal full backups do not ever break the log chain.
Copy_only full backups don't reset the differential base, that's the difference between copy_only and normal."copy-only backups do not impact the log chain" is what I was referring to.
Copy-only backups do not impact the log chain, that's correct, however it's a meaningless statement, because normal full backups do not impact the log chain. No full or differential backup impacts the log chain (except by starting one where one does not exist)
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
July 12, 2017 at 8:37 am
GilaMonster - Wednesday, July 12, 2017 8:27 AMcoolchaitu - Wednesday, July 12, 2017 8:17 AMThanks for replying Gail. So we cannot take full backup of read-only databases as it resets the differential base.No. We cannot take full backups of readable secondaries, as it would require changing a database that cannot be modified (because it's in an AG)
However, I came across this link https://technet.microsoft.com/en-us/library/ms190374(v=sql.105).aspxhttps://technet.microsoft.com/en-us/library/ms190374(v=sql.105).aspx , which says we can take full backup of read-only databases. I am confused. Could you please clarify
A read-only database cannot be modified. Therefore there cannot be anything changing the extents in teh database and setting bits in the differential bitmap, therefore the differential base doesn't need to be reset, as nothing can have modified the database since it went read-only
I did not get it Gail. So, can we take full backup of read-only database? Its mentioned like that in the link i provided.
July 12, 2017 at 8:42 am
coolchaitu - Wednesday, July 12, 2017 8:37 AMGilaMonster - Wednesday, July 12, 2017 8:27 AMcoolchaitu - Wednesday, July 12, 2017 8:17 AMThanks for replying Gail. So we cannot take full backup of read-only databases as it resets the differential base.No. We cannot take full backups of readable secondaries, as it would require changing a database that cannot be modified (because it's in an AG)
However, I came across this link https://technet.microsoft.com/en-us/library/ms190374(v=sql.105).aspxhttps://technet.microsoft.com/en-us/library/ms190374(v=sql.105).aspx , which says we can take full backup of read-only databases. I am confused. Could you please clarify
A read-only database cannot be modified. Therefore there cannot be anything changing the extents in teh database and setting bits in the differential bitmap, therefore the differential base doesn't need to be reset, as nothing can have modified the database since it went read-only
I did not get it Gail. So, can we take full backup of read-only database? Its mentioned like that in the link i provided.
It's effectively a copy_only backup, which you can take from the secondary. A regular full backup on a regular, non-readonly, database, modifies that database as part of the backup. A read only database can't have differentials or log backups, so the behavior of the backup is the same as copy_only, which you can do on your secondary.
What you're trying for is to have a full backup that you can then use to restore, with logs or differentials. However, since that secondary can't mark the database, as Gail explained in more detail, you can't do this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 12, 2017 at 9:14 am
Grant Fritchey - Wednesday, July 12, 2017 8:42 AMcoolchaitu - Wednesday, July 12, 2017 8:37 AMGilaMonster - Wednesday, July 12, 2017 8:27 AMcoolchaitu - Wednesday, July 12, 2017 8:17 AMThanks for replying Gail. So we cannot take full backup of read-only databases as it resets the differential base.No. We cannot take full backups of readable secondaries, as it would require changing a database that cannot be modified (because it's in an AG)
However, I came across this link https://technet.microsoft.com/en-us/library/ms190374(v=sql.105).aspxhttps://technet.microsoft.com/en-us/library/ms190374(v=sql.105).aspx , which says we can take full backup of read-only databases. I am confused. Could you please clarify
A read-only database cannot be modified. Therefore there cannot be anything changing the extents in teh database and setting bits in the differential bitmap, therefore the differential base doesn't need to be reset, as nothing can have modified the database since it went read-only
I did not get it Gail. So, can we take full backup of read-only database? Its mentioned like that in the link i provided.
It's effectively a copy_only backup, which you can take from the secondary. A regular full backup on a regular, non-readonly, database, modifies that database as part of the backup. A read only database can't have differentials or log backups, so the behavior of the backup is the same as copy_only, which you can do on your secondary.
What you're trying for is to have a full backup that you can then use to restore, with logs or differentials. However, since that secondary can't mark the database, as Gail explained in more detail, you can't do this.
Thanks for replying Grant. So, in the link https://technet.microsoft.com/en-us/library/ms190374(v=sql.105).aspx, they are talking about copy_only full backups?
July 12, 2017 at 9:16 am
coolchaitu - Wednesday, July 12, 2017 9:14 AMGrant Fritchey - Wednesday, July 12, 2017 8:42 AMcoolchaitu - Wednesday, July 12, 2017 8:37 AMGilaMonster - Wednesday, July 12, 2017 8:27 AMcoolchaitu - Wednesday, July 12, 2017 8:17 AMThanks for replying Gail. So we cannot take full backup of read-only databases as it resets the differential base.No. We cannot take full backups of readable secondaries, as it would require changing a database that cannot be modified (because it's in an AG)
However, I came across this link https://technet.microsoft.com/en-us/library/ms190374(v=sql.105).aspxhttps://technet.microsoft.com/en-us/library/ms190374(v=sql.105).aspx , which says we can take full backup of read-only databases. I am confused. Could you please clarify
A read-only database cannot be modified. Therefore there cannot be anything changing the extents in teh database and setting bits in the differential bitmap, therefore the differential base doesn't need to be reset, as nothing can have modified the database since it went read-only
I did not get it Gail. So, can we take full backup of read-only database? Its mentioned like that in the link i provided.
It's effectively a copy_only backup, which you can take from the secondary. A regular full backup on a regular, non-readonly, database, modifies that database as part of the backup. A read only database can't have differentials or log backups, so the behavior of the backup is the same as copy_only, which you can do on your secondary.
What you're trying for is to have a full backup that you can then use to restore, with logs or differentials. However, since that secondary can't mark the database, as Gail explained in more detail, you can't do this.
Thanks for replying Grant. So, in the link https://technet.microsoft.com/en-us/library/ms190374(v=sql.105).aspx, they are talking about copy_only full backups?
No. They're talking about regular full backups.
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
July 12, 2017 at 9:24 am
GilaMonster - Wednesday, July 12, 2017 9:16 AMcoolchaitu - Wednesday, July 12, 2017 9:14 AMGrant Fritchey - Wednesday, July 12, 2017 8:42 AMcoolchaitu - Wednesday, July 12, 2017 8:37 AMGilaMonster - Wednesday, July 12, 2017 8:27 AMcoolchaitu - Wednesday, July 12, 2017 8:17 AMThanks for replying Gail. So we cannot take full backup of read-only databases as it resets the differential base.No. We cannot take full backups of readable secondaries, as it would require changing a database that cannot be modified (because it's in an AG)
However, I came across this link https://technet.microsoft.com/en-us/library/ms190374(v=sql.105).aspxhttps://technet.microsoft.com/en-us/library/ms190374(v=sql.105).aspx , which says we can take full backup of read-only databases. I am confused. Could you please clarify
A read-only database cannot be modified. Therefore there cannot be anything changing the extents in teh database and setting bits in the differential bitmap, therefore the differential base doesn't need to be reset, as nothing can have modified the database since it went read-only
I did not get it Gail. So, can we take full backup of read-only database? Its mentioned like that in the link i provided.
It's effectively a copy_only backup, which you can take from the secondary. A regular full backup on a regular, non-readonly, database, modifies that database as part of the backup. A read only database can't have differentials or log backups, so the behavior of the backup is the same as copy_only, which you can do on your secondary.
What you're trying for is to have a full backup that you can then use to restore, with logs or differentials. However, since that secondary can't mark the database, as Gail explained in more detail, you can't do this.
Thanks for replying Grant. So, in the link https://technet.microsoft.com/en-us/library/ms190374(v=sql.105).aspx, they are talking about copy_only full backups?
No. They're talking about regular full backups.
But how is it possible? In the link, they are talking about regular full backups but you are saying regular full backups cannot be taken for read-only databases.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply