Post reply

Changes after last transactional backup

  • September 7, 2009 at 9:53 am

    #209744

    If I make a transactional backup, I know that contains the changes after last backup.

    Exists any types of query that I can make to obtain the rows that change after last backup?

    I want to query all tables and I want to see rows that have changed after last backup.

    Thanks a lot

    Viky.

  • September 7, 2009 at 11:34 am

    #1050185

    this will be very hard achive this goal ;/

    in SQL 2008 you can use CDC/CDT (Change Data Capture or Change Data Tracking) - maybe you can use these?

  • September 7, 2009 at 2:05 pm

    #1050215

    I use SQL 2005, Is it possibble in this version ?

    Viky.

  • September 8, 2009 at 6:34 am

    #1050451

    Not really - not without writing an awful lot of custom code anyway.

    It's probably not what you want, but Paul Randall published a script some time ago to estimate the size of the next differential backup (which would contain just the pages that changed since the last full backup - not since the last transaction backup, sadly).

    http://www.sqlskills.com/BLOGS/PAUL/post/New-script-How-much-of-the-database-has-changed-since-the-last-full-backup.aspx

    This later post shows that he is working on a since-the-last-transaction-log-backup version:

    http://www.sqlskills.com/BLOGS/PAUL/post/New-script-how-much-data-will-the-next-log-backup-include.aspx

    This is extremely hairy stuff (just warning you!)

    Marcin's suggestion is a good one (in 2008), it just depends on *exactly* what you are after...

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • September 8, 2009 at 11:07 am

    #1050658

    Nice links Paul.

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • September 8, 2009 at 2:05 pm

    #1050781

    If all your tables had a date column for when they were updated you could compare that date to max(backup_finish_date) in msdb..backupset

    ---------------------------------------------------------------------

Viewing 6 posts - 1 through 5 (of 5 total)

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