I recently inherited a poorly-managed SQL server.
One of the databases is in rough shape. Here are the details:
- The .mdf file is a modest 369MB.
- The .ldf file is a bloated 29.7GB.
- The recovery model is set to FULL.
- The data file is backed up nightly without issue, and is 365MB.
- The transaction log has NEVER been backed up.
I've restored the nightly backup to the same SQL server and poked around in it to make sure data looks to be intact. When I restore that nightly .bak backup file it recreates the .mdf and .ldf files at 369MB and 29.7GB, respectively.
Being decent with SQL, but not what I would call an expert by any means, I have a few follow up questions on this situation that I am hoping to get feedback on:
- How is a 369MB .bak file restoring both a 369MB .mdf file and a 29.7GB .ldf file?
- Any suggestions on what to do regarding the recovery model and bloated .ldf file? We are only concerned about doing backups once nightly - we don't need to do differential backups at this time - but it's something I will tackle later.