April 16, 2018 at 9:44 am
Hi,
I am a little new to this, but I have been trying to find a good explanation of the different backups.
And while they will give me time and point that they will back up to, none so fare ahs explain what they are. Let me explain: Maybe they are actually all the same in what they back.
For example, I would like to know what transaction log is backing up verses a full back up. Is it just the transactions. does it have anything to do with the LDF file. and what does a full back up contain.
One other question here, a lot of the sites seem to be saying that you cannot do a transaction log back up unless your database in set to Full or Bulk_ log; so it would not work if set to Simple. Do I have this right?
Thank you
April 16, 2018 at 1:31 pm
I'm going to label each q so others can comment without having to repeat the full q:
A1) transaction log back up verses a full back up?
A tran log backup consists of all committed transactions that have occurred since the last tran backup. The backed-up parts of the log are then marked as "available for reuse", that is, that space can be written over with new transactions.
A full backup backups all data plus as much tran log data as is needed to make the db backup consistent. But it does NOT do a full tran log backup and does NOT mark any log space as reusable.
A2) [Is a tran log backup] just the transactions?
Yes.
A3) does it have anything to do with the LDF file?
Yes. Transactional data is stored in the .LDF file. And .LDF file space is what is marked as "reusable".
A4) "You cannot do a transaction log back up unless your database in set to Full or Bulk_ log; so it would not work if set to Simple". Do I have this right?
Yes. In simple mode, SQL automatically marks log space as reusable, without log backup. So tran log data could be overwritten right away. Since tran log backups are useful only if ALL log data is available, with EVERY trans in order, a log backup in simple mode is useless. SQL prevents you from doing a log backup so that you don't mistakenly believe that you are creating a usable backup.
B1) what does a full back up contain?
A full backup backups all data plus as much tran log data as is needed to make the db backup consistent to the time of the full backup. For example, if a trans had started before/during the backup, but had not finished, SQL would roll that trans back so you didn't see partial updates in the restored backup.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
April 16, 2018 at 4:37 pm
This was really a great explanation, thanks this really helps.
Thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply