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?
Best Answer
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.