locked
In simple recovery mode can I do live differential backup? RRS feed

  • Question

  • I have a small, ~10GB SQL 2008 R2 database, that was setup with simple recovery.  We do full backup each night at midnight when no one is using the database.  Is there any problems with doing differential backups during the day when users may be writing to the database?  Could I even do hourly differential backups while users are using the database?  I'm conflicted about switching over to full recovery mode and using transaction logs to have the ability restore data between backups.  If I can do a couple daily differential backups while users are using the database during the day, in addition to our nightly full backups,  than I  live with simple recovery mode.  

    Thank you

    Tuesday, June 30, 2015 8:57 PM

Answers

  • Yes, there are no problems doing differential backups while in simple recovery. If such a restore strategy fully satisfies your RPO and RTO then you would be fine.

    I would still question why you aren't doing log backups, however. Why settle for some 2-3 restore points per day when you can restore down to the millisecond?


    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, June 30, 2015 9:49 PM
  • Note also that depending on what operations you perform, your diff backups can be close to the size of the full backups. Particular, you should run any reindex jobs before the full backup.

    This is much less of a problem if you run log backups. Also, if you run log backups, and archive them properly, you can recover from a scenario where corruption is introuced on Monday, but not discovered until Thursday. You can recover without data loss by restoring the backup from Sunday night + log backups. With full + diffs, you must start with the latest full backup.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, June 30, 2015 10:21 PM

All replies

  • Yes, there are no problems doing differential backups while in simple recovery. If such a restore strategy fully satisfies your RPO and RTO then you would be fine.

    I would still question why you aren't doing log backups, however. Why settle for some 2-3 restore points per day when you can restore down to the millisecond?


    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, June 30, 2015 9:49 PM
  • Note also that depending on what operations you perform, your diff backups can be close to the size of the full backups. Particular, you should run any reindex jobs before the full backup.

    This is much less of a problem if you run log backups. Also, if you run log backups, and archive them properly, you can recover from a scenario where corruption is introuced on Monday, but not discovered until Thursday. You can recover without data loss by restoring the backup from Sunday night + log backups. With full + diffs, you must start with the latest full backup.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, June 30, 2015 10:21 PM