Incremental backup set, how better setup changes/new tracking?

Question

Incremental backup set, how better setup changes/new tracking?

mario17

SSCertifiable

Points: 6078
More actions
November 25, 2014 at 10:09 am

#313402

Hi,
I'm bit new for this, for now I see that we can do trigger + setup kind of audit table with all content to be backed.
I suspect that it should be some build in feature in MS?
Appreciate you feedback/links
Thanks
Mario

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

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

mario17 (11/25/2014)
Hi,
I'm bit new for this, for now I see that we can do trigger + setup kind of audit table with all content to be backed.
I suspect that it should be some build in feature in MS?
Appreciate you feedback/links
Thanks
Mario

There is auditing in SQL Server, but it is kinda unusable IMHO.

ApexSQL and Idera offer "compliance" or "audit" capabilities if you want a point-and-click 3rd party solution.

Enterprise Edition of SQL Server has Change Data Capture which can be used for auditing, but also has issues/limitations/gotchas.

Building your own auditing system using simple triggers and tables is a fairly easy thing to do (although you best do things right or you get lots of trouble) and can be scripted to automatically generate all necessary objects off of system tables such as sys.objects, sys.columns, etc.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 2

But if you're looking for backup, I'd strongly suggest using the built in backup mechanisms. Full backup, differential backup and log backups combined can ensure very little or even zero data loss. Also, if you're just looking at having a copy of the data as a backup, you could look at mirroring, log shipping, or, if you have Enterprise available, availability groups.

"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

mario17 SSCertifiable Points: 6078 More actions · Answer 3

Thanks much TheSQLGuru and Mr.Fritchey !!!!

Going to check both options starting with build in, it's good start for me.

Best

Mario

mario17 SSCertifiable Points: 6078 More actions · Answer 4

Hi, all

After making myself very confy around log and diff backups, I now looking how to make it easier: how to bring that increments but only from single table to my ETL staging db,

I really don't need anything else just last update for N hours for TableA, and need to add them into ETLTableA on different db.

If I deal with bkups I still need to operate on db level, is there any option to catch only updates on TableA, or the only solution is to do own triggers/audit tables.

I really appreciate all you help and links.

Thanks

Mario

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 5

Nope. Backups are just databases. If you want to move only a table you need to set up some kind of ETL process. The common tool is SQL Server Integration Services (SSIS).

"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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

mario17 (12/2/2014)
Hi, all
After making myself very confy around log and diff backups, I now looking how to make it easier: how to bring that increments but only from single table to my ETL staging db,
I really don't need anything else just last update for N hours for TableA, and need to add them into ETLTableA on different db.
If I deal with bkups I still need to operate on db level, is there any option to catch only updates on TableA, or the only solution is to do own triggers/audit tables.
I really appreciate all you help and links.
Thanks
Mario

ApexSQL has tool(s) that can capture and offer up information (including redo/undo scripts) about activity that shows up in the transaction log. It is either that or triggering I think. Well, if you are enterprise edition you can use CDC or Change Tracking too.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service