SQL Server Backup and Restore Incrementally

I am investigating a reporting solution for a production database.

We currently have a production db which backs up fully at 01:00 each day. This backup is then copied using a Robocopy job to a separate SQL db which restores it.

We then report on the second database in order to keep some extensive queries away from production.

I am hoping someone can help me with my thoughts - is it possible instead of having our current setup - to copy only updated or newly added rows across incrementally (note not deleted - I need these to stay in the second database)?

Thanks in advance.

From the way I see it, you have two pretty good options here:

• Replication

• Log Shipping

If you setup transactional replication you could have your data modifications replicated to a subscriber database (or databases).

With log shipping it essentially would be transferring transaction log backups (note, you'd need to have your database in the full recovery model to take transaction log backups). Then you could have your secondary database in standby mode, allowing for read-only traffic.

The way I would choose between these two options is the level in which you want reporting and data movement to your secondary server. Are you reporting on the entire database? In that case, I'd opt for log shipping. But if you're primary reporting on a single table or just a handful of tables and you don't necessarily want the entire database's footprint living on the secondary server, then replication would be a better option.

EDIT

As per your comments below, it looks like you don't want to transmit DELETE operations to your reporting server. In this case, Log Shipping wouldn't meet the requirements because all logged operations would be on the reporting server, including your DELETEs.

What you can do with replication is set it up so that it only replicates INSERT and UPDATE operations.

See the below screenshot. Basically when you create your publication, you can set many properties for each article. One of the options is what action to take for each operation: INSERT, UPDATE, and DELETE. All you need to do is specify to not replicate DELETE operations:

StackExchange Database Administrators Q#56270, answer score: 4