I recently had an incident that got me rethinking my backup plan: One of our MSSQL driven aplications stalled because the log fle had grown too big on the filesystem (it was 50 GBs IIRC).
It turned out that the aplication developer is setting the DB recovery method to 'Full', and the full backup nighlty routing does not purge and shrink that log file. In order to shrink it had to backup it up, change the recovery method to 'Simple', and backup with shrink.
I obviously want to prevent that from happening, so I am looking for options.
This is an MSSQL 2012 Express x64 instance.
The application developer is totally non-cooperative (external contractor, works against everyone) so I am assuming he wouldn't want me to leave the DB in 'simple' mode.
I could easily tune my sqlcmd scripts to do a "backup in 'full' -> switch to 'simple' -> backup in 'simple' -> revert to 'full'" and keep the file at bay, but I fear I could be running some risk I am not foreseeing right now.
It seems to me that I should do this relatively often, as the larger the log file grows the more time it takes to backup and shrink, causing noticeable slowdowns.
If that routine is reasonable, and after each cycle I end up with 'a backup prior to purging the log' and 'a backup after the log was purged', and assuming both proceses exit without error, would it be wise to discard the first one? I would be keeping the 'data' backup and the 'purged log' backup.
I should add that AFAIK by doing "full backups" but not "regular log backups" I am sacrificing the possibility to revert to "states between two full backups", but I am not hindering the full backups themselves, as I have restored and cloned them many times in the past without issues. But if this is wrong please do let me know.
Note: For simplicity's sake let's say that -for now- being able to revert to a nightly full backup is good enough.
I hope I was able to explain myself. Thanks in advance.