How to implement incremental and differential backup?

1

Hello

Let's say you connect to a db via ODBC or something, how can you mirror that data to your own SQL Server using incremental and differential backup?

So basically, I want:

  1. only pull the changes from the source db and not the whole data each time, where changes can be insertions/deletions/edits.
  2. create an archived version of records when they are changing so that I know when each version of the record existed

Are the above something that can be easily done via SSMS or Azure Data Studio or something? What is the easiest and most flexible approach to achieve them?

Thanks!

2

Nope - can't be easily done, which is why there is a whole industry based on doing what you are asking about. Lookup Kimball Group - they have a lot of information about data warehouse concepts.

1 Like
3

For item #1:

Or:

For item #2:

None of these are things you use from SSMS/ADS, you'd configure them as administrative jobs and have them run independently, on whatever schedule you need. An end user would only query the replica instances and have no knowledge of the backend processes.