Our production database gets backed up every night.
I notice that we have a load of trn files in the DbBackups/Log folder - they are for the current month only so I guess they get cleared down every month.
If I have a full database backup as of midnight 3rd September, is it safe to delete all of those trn file that pre-date that?
It depends - are those files being copied off to another location? Do you only have the one full backup on the 3rd - and how often are you backing up the database?
If your latest full backup file is corrupted - do you have the previous full backup file available to recover?
You need an unbroken chain of log backups from a full backup to current point in time to recover the system in the event of a disaster. If the current full backup is not available or corrupted - you would need the previous full backup and all transaction log backups from that full to current point in time - if you don't have that then you are not able to recover the database without data loss.
Do you mean .bak files for trans log, or a .trn file that is the log file? You can't remove the actual log files, of course, and the system shouldn't allow you to do it.
If you mean backup of log files, then that will depend on the details of how you do recovery and what recovery scenarios you want to allow. If you are only ever worried about recovering to the latest time, then you don't need log files prior to your last verified full backup.
If you're not doing a test-restore at least once per week, you're making a mistake. Remember, backups by themselves are useless... it's the restores that count.
Sorry if this is a stupid question.......are there any tools out there that can analyse a backup and say "this is good" or "this is corrupt". Our databases are typically 220GB and I imagine IT will push back on a weekly test-restore.
RESTORE VERIFYONLY can tell you if it thinks the file can be restored, but the only sure way to prove the backup file is good is to actually perform a restore from that backup file.
You should ask for the space somewhere to do the RESTORE and DBBC CHECKDB copying in the person you report to. The IT cost is probably mininal compared to the possibility of loosing the data. If you get push back, at least the potential problem has been raised.
Even most nVME SSD drives have more than 220GB nowadays and, especially compared to losing data, a relatively cheap to buy. Put together a plan that includes up to 5 years of growth and then remember that the answer is always "No" unless you ask.
Also, make sure that you're monitoring "Suspect Pages" for each instance. See the following link for more on that and remember that's "in addition to" and not "instead of".