I have a SQL Server 2012 Std Edition installed, contain 1 database in full recovery mode, so the log grown up quickly, but I just need a PIT recovery in past 7 days.
Can I schedule a full database backup every day (rolling 7 days) and truncate the t-log right after the backup job ? or what is the best way to keep the file size as small as possible ?
Best Answer
If you need to be able to restore to any point in time then you need to be in full recovery mode (which you are). When in full recovery mode, you need to do transaction log backups in addition to your full backups.
Doing a Full Database backup does not make any difference to your log file, only a transaction log backup will clear it out and allow you to reuse the space.
When you're in full recovery mode NEVER truncate your transaction log - this will break the backup chain and you won't be able to do a point in time restore.
Transaction Log backups can run as frequently as you want or as often as you need to keep the file from growing bigger than your disk space or are required to by the business. I've seen some companies run them daily and some run them every minute and a lot in between. The advantage of running them more frequently is that if your disks die then you only lose data since your last log backup (assuming they're on different drives/hardware). It also keeps your log backup files smaller and the backup operation is usually faster because it has less to backup.
You can schedule a job to clean up old log backup files that you no longer need (e.g. in your case more than 7 days old).
If you need to restore from backups then you need to do the following:
Can I strongly suggest using Ola Hallengren's scripts if you're not using a 3rd party tool to do backups (https://ola.hallengren.com/)