excluding CDC enabled tables during a full backup of db

  • 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 !!

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply