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>'
There are multiple ways to get to the intended target recovery point with your setup.
A few things:
- You cannot eliminate log backups for the most important reason of transaction log re-use.
- It's not possible to know when an issue will occur and thus having multiple ways to get to the RPO is useful and sometimes necessary.
- Differentials aren't a substitute for log backups, they will lower the RTO as they are much faster to apply than log backups but ultimately aren't worth much on their own (spanning broken lsn points, etc, is useful)
- How are you going to know which ones you need and don't need ahead of time?
I would not stop taking log backups but you could put into place an aging mechanism in your backup software or process to eliminate files that are no longer needed.
Using your example, you have many paths but the basis is as such:
Always take a tail of the log backup first
- Restore the full backup, latest differential, all logs.
- Restore the full backup, all transaction logs.
This is more flexible as a corrupt backup file (say a log file) could be spanned by a differential or a corrupt full could be spanned by differentials, etc.
I would not change how your strategy is currently, but I would make sure it meets your RPO and RTO requirements.
Best Answer
I can't speak to 1 but for 2, I don't know that your position should be pushing the business toward a specific RPO (recovery point objective). They may not be aware that they'd have to re-enter all the data for a day if things go belly up. Talk to them, find out how much data loss they're willing to tolerate. If they say 24 hours is too much, great, then that indicates the current approach is insufficient for their needs. If that requires hardware purchase to meet the RPO, then they will need to provide funding or accept their current max data loss. Finally, document the outcome in some public place and then test on your restores a recurring basis to ensure you are able to meet that RPO.
That said, there are plenty of other reasons to have data and log (and temp) on separate drives. Some of them documented on this question https://serverfault.com/questions/38511/ms-sql-layout-for-best-performance