troubleshooting Question

log recovery questions

Avatar of Torus
Torus asked on
Microsoft SQL Server
5 Comments2 Solutions209 ViewsLast Modified:
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?
Database Consultant
Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros