Some documentation and examples concerning the full recovery model on sql server recommend to do a full database backup in combination with an additional log file backup.
My questions are now:
a) Is it necessary in principle to do the additional log file backup in order to restore the database. (This should be possible from the full database backup alone, shouldn’t it?)
b) Is it necessary to do the log file backup (even if it may be deleted afterwards) in order to free the space in the log file or is this accomplished automatically by the (full) database backup?
A FULL Backup actually copies the occupied data-pages into the BAK file, thus finally a BAK is - more or less - a 1:1 copy of the “net” database. The Transaction Log - FULL Recovery presumed - contains all information about committed and uncommitted transactions. Hence, when creating a LOG backup you could backup/save all changes since the last FULL backup - so you are minimizing the maximum potential data-loss in worst case, depending on the frequency of you LOG backups!
For a productive database I highly reccommend to create FULL and LOG backups, of course!
A FULL backup does not truncate the Log, thus to release the space from the Log - truncating the committed transaction data - you have to create LOG backups.
If you use SIMPLE Recovery then the Log is truncated automatically with every commit.
If you are only doing full backups, then you can only recover to that backup. If you are doing full plus log backups, then you have the ability to recover to any point in time.
if you don’t want to run transaction log backups, and all you need is a daily full backup, then set the recovery model to simple. If you don’t run transaction log backups in full recovery model, the log will continue to expand until you run out of disk space.
That’s essentially how I understood this recovery model. So normally you would do log backups periodically and db-backups from time to time like this, e. g.:
logBackup1, logBackup2, logBackup3, dbBackup1, logBackup4, logBackup5, logBackup6, dbBackup2,…
and you can delete any old logBackup that has been done before a dbBackup was made. So in order to save disk space (used by the stored logBackups) it would be reasonable to make a dbBackup immediately after a logBackup and not (as is recommended in some backup plans) a logBackup after a dbBackup, wouldn’t it?
According to another posting of yours I guess that you are mixing something up …
A FULL backup is used to save the whole database, the LOG backup is intended to save the “delta” - change of data - since the last FULL (or DIFF) backup.
Typically you do at least one FULL per day and maybe LOG backup every hour, up to ever 5 minutes or so.
As backing up the whole DB is disk- and time consuming it is nonsense to run a FULL backup after each LOG backup (which runs pretty quick and includes way less data - depending on the LOG backup frequency). Of course, once you have created a FULL backup - which includes all the data and changes since the last FULL backup - you could get rid of all previously created backups: old FULL and LOG backups.
The LOG backup is also required to truncate the log. So if you are using FULL Recovery you have to do a LOG backup sooner or later.
So finally it’s all a matter of timing; and this depends on the questions you have to answer yourself:
- What is the maximum acceptable Data Loss?
- What is the maximum acceptable Server Downtime?
Last but not least I reccommend to read the SQL Server “Books Online” about backup strategies etc…
Thanks, Jörg, for your explanations.
Of course it is not reasonable to make a db-backup after each log-backup. But in the case when a db-backup is necessary (or part of a backup-plan), then - normally - a log backup is done also at the same time. And now the questions arises, whether it is reasonable to make this log-backup before or after the db-backup. And, as we have worked out, it makes more sense to do the log-backup before the db-backup, because in this case, one can drop (delete) the log-backup shortly after the db-backup has finished in order to save disk space.