2

We currently have a database in SIMPLE recovery and are planing on setting up an Availability Group for reporting purposes (Enterprise SQL 2016). My understanding is that this will require moving the database to FULL recovery. We have no need of the log backups and no interest in storing them so I was thinking about setting up the log backups to NUL (bit bucket). Nothing I've read has said I'll have any problems with this but I wanted to make sure I'm not missing anything.

3
  • ✋ I've done this. Works just fine.
    – AMtwo
    Jun 1, 2017 at 22:30
  • How does an AG enable reporting? Jun 2, 2017 at 3:46
  • You make a secondary that you use as a read only location for reporting. Jun 2, 2017 at 14:06

2 Answers 2

2

I would still suggest to take log backups (not to NUL) and have a delete process to delete then after x hours.

If for some reason, your both servers are down & your primary backup is corrupted. So in this worst case, you will have a possibility of restoring the db provided you have preserved the log backups.

If the db can be recreated and is not that critical, go for discarding the log backups to NUL.

See this : http://sqlinthewild.co.za/index.php/2009/08/31/backing-up-to-nul-vs-backup-with-truncate-only/

4
  • We will absolutely still be taking regular FULL and DIFF backups and making sure to keep them. It's just the LOG backups I don't feel like we need. The business is good without point in time recovery currently and I don't see that changing. Jun 1, 2017 at 19:42
  • 2
    Make them in writing .. when business says so ... Go for it once you have a written proof :-)
    – Kin Shah
    Jun 1, 2017 at 19:42
  • @KennethFisher If you're using Ola's scripts, just set the delete time to the space between Diffs. That way you're only keeping as much as you'd theoretically need if anyone changes their mind. How often are you planning on taking log backups on this thing, anyway? Jun 1, 2017 at 20:04
  • It's a long standing DB and we do have in writing that last nights backups are good enough (even farther back at need). We'll probably be taking the log backups every 15 minutes and then may make it more often depending on how things work out. Jun 1, 2017 at 20:15
3

No, you aren't missing anything except the opportunity to do a point in time recovery of the database. AG's protect you from MOST hardware failures but they don't protect from user errors that drop a table or truncate data. Even if the business didn't have a SLA for RPO I would have log backups running as a DBA just to cover things, but you can backup to DISK='NUL' and the log will clear just like you backed up to a regular disk file.

3
  • We aren't even using the AG for HA. Just for reporting. The business has literally said "We don't care about point in time, last nights backups are good enough". We even have it in writing :) Jun 1, 2017 at 20:13
  • Replication to Standard Edition would be much cheaper in licensing costs and wouldn't require FULL recovery for the database if it is just for reporting purposes. Jun 1, 2017 at 20:39
  • Because we are a large enterprise we have special licensing and believe it or not currently Enterprise licensing is actually cheaper than new standard licenses. Jun 1, 2017 at 20:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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