1

We do a full backup of our SQL Server 2012 database every night.

A new developer needs an incremental (or differential) backup of a backup we took one week ago. The reason is that for them, importing the data takes several days, as their destination is not SQL server and has to go through several transformations.

There have been 7 full backups since the last backup they imported.

How do I get them the incremental backup that they need? Do I use differential or log shipping and how do I account for that fact that the data they have is one week old with several full backups in between?

2
  • Is this going to be an on-going problem? Are you going to continue to send them data that they will need to ingest? Do you know how they are importing SQL Server backups without using SQL Server?
    – S3S
    Aug 23, 2019 at 20:26
  • For clarification:the reason we are doing this is because we are migrating the whole project away from SQL Server to MySQL and we are heading towards the launch. Importing the full database takes several days due to the number of data transformations we're having to do, so we want to only import the differences between the last full backup and now, until we launch in a few days' time.
    – UKcentric
    Aug 25, 2019 at 7:09

1 Answer 1

4

You cannot go back in time and get an incremental backup as of a certain time. If the database is in full recovery mode, you can give them the transaction log backups (if you have them) that go from the backup they restored to the time they want to restore to.

However, if they have been using the database, they will have to restore the backup again and leave it in recovery mode so that the transaction logs can be restored into it. Once a backup is restored and the database is taken out of recovery mode so it can be used, no more backups (differential, incremental, or log) can be restored to it.

You might also check to see what the developer actually wants as I suspect they used the wrong terminology in the request. Referencing the transformation, etc., it seems that maybe they are expecting to get a backup that will only restore the new records from the time period. No matter what backup you give them, they are going to get the entire database (unless you are just going to restore partitions, but based on the question I'm guessing that's not really a good option).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.