Insert only database backup - How to do it the right way?

SQL Server Administration
3

A few questions:

  1. Do you always need to query the entire data set?

  2. Can data be archived based on age, or some other criteria? (e.g. devices are removed/replaced and their associated measurements can be deleted or removed from normal querying)

I don't recommend using read-only filegroups, they won't help you if you're going to stick with the tables you have now.

Weekly or monthly full backups, with daily differential backups, will probably be fine. You only need to restore the last full backup, then the last differential backup, plus any log backups made after the last differential.

Unless you absolutely need to be able to restore to an exact point-in-time (less than 1 hour), you can probably set the database to Simple recovery and just do more differential backups. You could even have a rotating set of differential backup files (e.g. Backup_00_00.diff, Backup_01_00.diff) that you overwrite hourly with that time slot's differential backup, if you need to save disk space. You'd only use this for disaster recovery purposes, not for archiving data.

1 Like