I have some questions on the log recovery. I have a database which set to full recovery mode. The mdf and ldf are set to unlimited growth. However, the ldf will grow very fast. This is not I want. If I backup the database once a day and 3 hours on the ldf,
1. I don't want the ldf kept growing. Before I backup, I can truncate the ldf and shrink it?
If the problem occurs after backup, I can use the backup as a checkpoint to recover? I saw some article saying that if using backup, the log file will be truncated automatically, is it true?
2. for example, if I backup at 2:00 am , 4:00 am, 7:00 am, 11:00 am will backup the log and the mdf is corrupted at 1:00 pm before the next log backup, how can I use the backup mdf and logs and existing ldf to recover all data including those from 11:00 am to 1pm.
3. if I accidentailly delete some records, can I recover the data using the active log file? or it is really no way to recover?
4. Can I specify a time for the point of recovery? e.g. I just want to recover 10:00 am data, how can I use the 11:00 am backup log to just get back the data before 10:00 am?
5. How come if the database is set to full recovery, the log file will increase the size every time I shrink the database? it is normal?