May 8, 2013 at 11:19 pm
I have a database with approximately 4000 tables on SQL 2008R2 x64
Of those tables, approximately 70 are tracked by CDC (Change Data Capture) process.
The CDC job is running constantly across this database.
Question: when taking a nightly full backup of this database, is it possible to exclude the CDC enabled mirror tables ?
The CDC job is crashing virtually on a nightly basis, as of late. We'd like to rule out if the backup process is coming into play. Unfortunately, I cannot change the backup timings at the moment.
Thank you !!
May 9, 2013 at 4:18 am
A full backup is always a full backup of the entire DB. You can do things like filegroup backups and have the CDC tables in another filegroup, but that complicated restores as you will then need filegroup backups and log backups to restore the DB to a consistent state (and full recovery model)
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
May 9, 2013 at 10:00 am
Thank you for your reply.
Since this is a production database, we often use it to refresh non-production databases with it so the developers will have recent data to work with.
Sounds like using filegroups may be a viable option but perhaps restoring could be even a bigger challenge if I go that route.
I'll research further to see what else could be causing the cdc job to crash almost at the same time on a nightly basis.
May 9, 2013 at 10:15 am
Be careful, filegroup backups require that you do have backups of all the filegroups that you intend to restore (and I'm not talking about a dev refresh, more a disaster restore of production), plus you need all log backups from the oldest to the newest backup that you're using to restore. Please don't think that it's an easy way to back up half of the database and never back up the other half.
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