February 10, 2012 at 7:27 am
Am I correct in thinking that if you mark a file group as read only then these file groups would be excluded from the relevant backups?
I am fairly sure statistics wouldn’t be automatically updated on this older data and that the opportunity for partial restores/backups also becomes available?
February 10, 2012 at 8:15 am
aaa-322853 (2/10/2012)
Am I correct in thinking that if you mark a file group as read only then these file groups would be excluded from the relevant backups?
No. It'll be part of full backups. Not diffs because nothing's changed (but that's not because it's read-only, it's only because nothing has changed)
I am fairly sure statistics wouldn’t be automatically updated on this older data and that the opportunity for partial restores/backups also becomes available?
Stats aren't auto-updated on tables that don't change regardless of the read-write settings.
Benefits - file/filegroup restores in simple recovery (as well as in full/bulk-logged where it's possible for read-only and read-write filegroups). No locks taken when querying the data in the read-only filegroup.
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
February 10, 2012 at 8:48 am
Sorry, yes I should have been more specific when I said 'relevant backups'
February 10, 2012 at 9:29 am
The fact that it's read-only is not what affects its presence/absence in a diff backup. A read-write filegroup that's had no changes since the last full backup also won't get backed up in a differential.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply