First, a bit about the log(LDF file) in SQL Server. This isn't necessarily a blow-by-blow recording of everything that has been done in the database, but instead is more of a "scratch pad" where work that is in-flight is stored. Periodically, this in-flight work is truncated from the log file when certain events occur (checkpoints).
In regards to your #2 question, this is a matter of understanding how switching between the different recovery models affect the log chain. Going between SIMPLE and FULL modes fundamentally affects how SQL Server handles the transaction log, so whenever you do this you need to take a full backup to "reset" that log chain so SQL Server knows how to properly handle the recovery model. In your example, SQL server was handling the log as if it was in SIMPLE mode immediately after the switch and it wasn't until you took your full backup and reset things that SQL server began to handle things differently.
If anything takes place that could cause your databases to stop and be inconsistent, the log becomes very important in bringing those databases back in a usable state. This recovery happens when the the database is brought back online. SQL Server will first bring up the databases, then apply any in-flight transactions (transactions that haven't been truncated) to those databases. This allows for your database to be consistent with the time that it was taken offline. However, we can't bring those databases back online to a specific point in time this way.
Taking this into account, we need to understand what point in time recovery is for SQL Server. When you recover a database, you are restoring from a database backup. If you want this recovery to be point in time, you need the following:
- The database you wish to recover was in FULL or BULKLOGGED recovery mode.
- You have a full database backup to start with.
- You have an unbroken series of transaction log backups taken after the full backup.
Using these, you can then use your database RESTORE syntax to restore the database to a specific point in time. The basic syntax is:
Restore your full
RESTORE DATABASE foo
FROM DISK='<physical file location of backup>'
WITH NORECOVERY
Restore your logs in order, repeat for each log up to the last one
RESTORE LOG foo
FROM DISK='<physical file location of backup>'
WITH NORECOVERY
Restore your last log, where you want to stop at a specific time
RESTORE LOG foo
FROM DISK='<physical file location of backup>'
STOPAT='<date/time of stopping point>'
For example, the .bak file reads 14GB but due to the transaction log the file is actually 60+GB.
Your problem is an inadequate maintenance and backup/recovery plan. You are not taking log backups with enough frequency, this is why your log grows through the roof. Start taking log backups to allow truncation and then shrink the log. You'll have a better recovery plan and a smaller database log file.
As for the question: no, there is no way to change the restored database file size(s). The restored database will always have the exact same layout as the original backed up database. Recovery mode has nothing to do with this, your restore would need 60GB on any recovery model. You're only mixing the recovery model in the discussion because you shrink the log by switching to SIMPLE. Read the linked article for more details on this aspect.
Best Answer
Yeah, you need that .ldf file. In the future, don't delete that.
If your database is running in full recovery, you need to run log backups regularly. "Regularly" is a term that depends on your particular environment, but hourly during business hours is a common choice. Basically, it only purges the transactions out of the log file when it's backed up. If it's never backed up, the log grows until it consumes the entire disk, as you've seen.
In the future, set the database to simple recovery, shrink the transaction log, and then back it up immediately, as NathanC suggested.
As for now: I don't know what kind of backups you have beyond the one you restored from, but I agree with NathanC that you need to let it sit and wait for the restore to finish. It's chugging through that log file. Once it finishes, truncate the log and set it to simple like your other databases, or implement log backups.