3

I've seen and read a lot of questions about the transaction log and I know that backing it up to nul is usually frowned upon, however my understanding is that this is from a data recovery perspective. As there is a lot of transient data passing through this system the logs are changed quite rapidly and can grow quite large. In order to save disk space I do not want to keep data that has no intrinsic value.

The Setup We have two SQL servers running SQL Server Mirroring, these are important from a usage (up-time) perspective but the data is transient and for the most part unimportant.

We have a 24 hour RPO on the servers but a 5 minute RTO.

What I Do In order to manage the transaction log size I run a log backup every 15 minutes to backup the log file to NUL as below.

BACKUP LOG [DatabaseName] TO DISK = 'NUL:';

So my question is thus: Other than not being able to recover data are there any other potential issues that could occur by backing up to NUL in a mirrored environment?

  • 1
    I suggest you change recovery model to simple instead of keeping it in full and throwing away contents of log in NULL. – Shanky Oct 6 '15 at 9:04
  • 1
    @Shanky I can't have the database in Simple as I need the mirror and mirrors only work with full recovery as the log is shipped to the mirrored pair. – Guy Oct 6 '15 at 9:07
  • Sorry but that was not quite clear from your question. Anyways I dont think there would be any issue in SQL Server as such . This command kind of do a backup your log and deletes it. Or you can say backup to a place which is Bottom less pit which is equivalent to throwing in black hole. – Shanky Oct 6 '15 at 9:13
  • @Shanky to help me learn and understand what how was I unclear that I was using a mirror? I made sure that I had that in both the title and on the line that asked the actual question? Also I used NUL and not NULL as the SQL command to backup is to NUL and not to NULL. – Guy Oct 6 '15 at 9:20
  • Without dragging this any further I thought Mirroed pair as mirroed backup. What you are referring to is called as Mirroring which I confused with former. Yes you are correct about NUL – Shanky Oct 6 '15 at 9:25
1

If you are complying with all the RPO and RTO requierments by having database mirroring in place, with daily full backups then there are no data consistency issues with making backups to NUL. So the answer to your question is No.

But (there is always a but) you are not having a secondary restore path if a full backup is corrupted and both the servers go down and your restore options are always the point in time when the daily backup is made.

By making normal log backups and storing them for 48 hours you can restore past a single corrupted full backup which is not possible to do with differential backups.

  • thank you I hadn't thought of the failed backup option. I will present this to the business and let them decide if the additional disk\tape space cost is warranted. – Guy Oct 6 '15 at 11:57

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.