I read some Books Online about recovery/backup, one stupid question, if I use full database backup/full recovery model, for backup operation itself, will it generate any additional transaction log to source database server? Will full recovery operation generate additional transaction log to destination database?
+1
A:
I'm not sure I completely understand your question, but here goes. Keeping your DB in Full Recovery mode can make your transaction logs grow to be very large. The trade off is that you can restore to the point of recovery.
The reason that the transaction logs are larger than normal is ALL transactions are fully logged. This can include bulk-logged operations, index creation, etc.
If drive space is not a concern (and with drives being so inexpensive, it shouldn't be), this is the recommended backup approach.
GregD
2009-03-05 01:59:12
1.Thanks GregD, there are 3 recovery mode, simple/full/bulk logged, what do you mean mode "The trade off is that you can restore to the point of recovery."? I feel it does not belong to the 3 modes. :-)2.Full database backup itself will not generate additional transaction logs for source db?
George2
2009-03-05 02:11:28
"The reason that the transaction logs are larger than normal is ALL transactions are fully logged." -- you mean recovery process has its own transaction log and will log together in the destination database transaction log file?
George2
2009-03-05 02:13:06
In Full Recovery, you can restore up to the time that your DB failed because you're using Full/Diff/Trans Logs to recover. Simple recovery model doesn't allow for that because you don't backup the trans logs.
GregD
2009-03-05 02:17:19
Hi GregD, I agree and understand your reply. But it is answer for which of my questions? :-)
George2
2009-03-05 03:57:31
The act of backing up does not generate additional transaction logs. Having said that, putting your DB into Full Recovery Mode, makes your transaction logs larger than if you leave your DB in Simple Recovery mode.
GregD
2009-03-05 04:17:02
What is the purpose of your question by the way?
GregD
2009-03-05 04:18:42
Thanks GregD, my question is overhead of full database backup/full recovery, especially to transaction log. Now I understand backup will never generate additional transaction log, but why full recovery mode will "makes your transaction logs larger" -- logs what additional operation for what purpose?
George2
2009-03-05 04:50:36
"why full recovery mode will makes your transaction logs larger" In Simple mode the records in TLog are made available for reuse as soon as each transaction finishes. In Full Recovery the transaction records are kept until a TLog backup is made - probably minutes or even hours later.
Kristen
2009-03-06 16:19:42
1.Thanks Kristen, is your comments documented somewhere in MSDN or somewhere? I am interested in the differences in how simple/full recovery modes treats logs.2.Will reovery process itself generates TLog?
George2
2009-03-08 11:48:02
"is your comments documented somewhere in MSDN or somewhere?" yeah, its all in the SQL Server documentation. There is no magic involved!!
Kristen
2009-03-08 12:55:31
"Will reovery process itself generates TLog" No, otherwise it would be impossible to issue a RESTORE on a database where the TLog file was full.
Kristen
2009-03-08 12:56:58
Thanks Kristen, I did not find answer from SQL BOL is that, during simple recovery mode, since backup will only backup database data part but no log part, if there is any committed changes (dirty page) which is not flushed to data file or changes which has not rolled back (continued...)
George2
2009-03-08 13:21:03
will the backup image from simple backup contains partial data from database which is not in a database consistent status?
George2
2009-03-08 13:21:35
Full backup (SQL 2000+) is the complete state at the END of the backup (SQL7 and earlier it was the state at the BEGINNING of the backup). To achieve this I believe SQL2000+ appends the Tlog within the Full Backup file. Only transactions that were committed when Full backup was made are restorable
Kristen
2009-03-09 16:11:51
+2
A:
A more useful view of this might be to say that Full Recovery prevents the contents of the transaction log from being overwritten without some other action allowing them to be overwritten
SQL Server will log most transactions (e.g. bulk load and a few others aside) and when running in simple recovery mode, effectively discard the newly created log contents at the end of the transaction associated with the creation of the same. When running in Full Recovery mode the contents of the trans log are retained until marked as available to be overwritten. To mark them as available to be overwritten one normally performs a backup (either Full or Trans Log).
If there is no space in the trans log and no logs contents marked as available to be overwritten then SQL Server will attempt to increase the size of the logs.
In practical terms Full Recovery requires you to manage your transaction logs, generally by performing a trans log backup every so often (every 1 hour is probably a good rule of thumb if you have no SLA to work to or other driver to determine how often to do this)
Karl
2009-03-05 02:20:35
Cool, Karl!1."A more view of this might be to say that Full Recovery prevents the contents of the transaction log from being overwritten without some other action allowing them to be overwritten" -- you mean when we online recover db, both db itself and recovery will generate transaction log?
George2
2009-03-05 05:42:44
2. "SQL Server will log most transactions and when running in simple recovery mode" -- when during recovery, what needs to be logged?3. "discard the newly created log contents at the end of the transaction associated with the creation of the same" -- is this safe to avoid data lost? why?
George2
2009-03-05 05:44:03
1/ Actions performed on the DB (Insert, update, delete) will generate content in the trans log. Recovering DB does not generate trans log, it makes use of them in some scenarios (point in time recovery is common)2/ During recovery (restore DB) nothing is logged to the trans log. 3/ see next
Karl
2009-03-06 01:23:14
Google or check Books Onlines for Simple and Full Recovery, Point in Time. This is an important question for you to answer. The answer depends on how much data loss you can stand and an understanding of the recovery models.
Karl
2009-03-06 01:23:49
If you are in simple recovery model you can only recover to the last backup taken. E.g. Back up at 00:00 hrs, DB lost at 08:00 hrs, 8 hours of work lost. In Full recovery model, Backup at 00:00 hrs, transactions logs backed up each hour. DB lost at 08:01, 1 minute of work lost.
Karl
2009-03-06 01:24:22
"To mark them as available to be overwritten one normally performs a backup (either Full or Trans Log). " Full Backup won't mark anything in the TLog as available - only a TLog backup (or forced clear!!) will do that - unless I have misunderstood what you meant?
Kristen
2009-03-06 16:14:34
"every 1 hour is probably a good rule of thumb" - different strokes for different folks, I'm sure, but I take the view that if you are bothing to keep a TLog, and back it up at all, you might as well back up frequently to give better recovery chance - I think that 10 minutes is a good trade-off
Kristen
2009-03-06 16:15:56
and helps to stop the Tlog growing too big - e.g. when database maintenance / re-index etc is running - which in turn hoggs disk space that is only needed rarely
Kristen
2009-03-06 16:17:07
Thanks Kristen, "Full Backup won't mark anything in the TLog as available" -- do you mean full backup does not truncate TLog and make log space avalable? I doubt it since full backup backs full TLog. Do you have any documents to prove it?
George2
2009-03-08 11:50:19
To Karl, if recovery itself does not generate log, if system fails in the middle of recovery, how to ensure database status is consistent and healthy (in the half of recovery process)?
George2
2009-03-08 11:51:53
Karl, "Full Recovery prevents the contents of the transaction log from being overwritten without some other action allowing them to be overwritten" -- you mean prevent overwritten during normal online process or during recovery process? (see next continued...)
George2
2009-03-08 12:04:49
"Prevent from being overwritten" feature of full recovery, you compare this feature with what kinds of recovery? Simple recovery? why?
George2
2009-03-08 12:05:32
"- do you mean full backup does not truncate TLog and make log space avalable?" Correct. Only TLog backup can do that - otherwise TLog restore would be dependant on having a specific Full backup - and it isn't. You can restore from ANY Full backup and then EVERY TLog backup since
Kristen
2009-03-08 12:52:20
"I doubt it since full backup backs full TLog. Do you have any documents to prove it?" Full backup includes the TLog at the end - so that you can restore just the Full backup. But it does NOT clear the Tlog file. See SQL Server documentation "Books Online"
Kristen
2009-03-08 12:53:17
Thanks Kristen, I read further about SQL Server BOL, and I want to confirm that only TLog backup type of all backup types will truncate TLog?
George2
2009-03-08 13:26:29
Another confusion is, I think even if full backup truncates TLog, if we have TLogs after the full backup, we could still recover database to any point after full backup. I did not see any advantages of making TLog not truncated in full backup?
George2
2009-03-08 13:28:58
George: If the recovery fails in middle, restart the process. Ideally you will have backup files on another disk (or better another server, on tape, etc)I believe that this discussion is getting to a point where it might pay for you to ask another question.
Karl
2009-03-09 00:37:12
I may have been incorrect that the Full backup marks Tlogs as available. I can see in BOL where it does imply that ONLY a trans backup will mark them and haven't tested thisBut the basic point is that Simply and Point in Time restores and Full And Simple Recovery Model are the key issues here.
Karl
2009-03-09 00:41:00
Thanks Karl, why full backup will not make TLog as available? I think available you mean truncate inactive parts of TLog. I think inactive part of TLog is always useless. Why full backup does not truncate them?
George2
2009-03-09 04:04:21
Gearoge2 - you aren't getting it. Full backup and Tlog backup are independent. Others TLog restore is dependent on a SPECIFIC Full backup. Therefore Full backup does not clear Tlog file. You can recover from ANY Full backup plus ALL later Tlog backups - APPLIED IN ORDER.
Kristen
2009-03-09 16:07:56