views:

1376

answers:

1

Hello everyone,

I have 2 confusions about SQL Server recovery and bulk recovery mode.

  1. whether recovery process itself will generate transaction log (here what I mean the logs are the logs generated by recovery process -- if any, and not generated by normal database DML/DDL operations)? Why?

  2. in the bulk recovery mode, whether the understanding of A or B is correct (if neither is corect, please help to correct me, here are my 2 different understandings which causes me confused)

A. the normal DML/DLL bulk operation on product database (not the recovery operation) will generate minimal amount of logs, since logs are minimal, the recovery process which utilizes log, can not recover any point in the middle of bulk operations; B. the normal DML/DLL bulk operation on product database will still generate normal large amount of logs (the same as non-bulk log recovery mode), but recovery process will treat bulk operation in transaction log as a unit to recover, which generates minimal logs for recovery process itself;

regards, George

+1  A: 

Please read the following two articles:

http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

http://www.simple-talk.com/sql/backup-and-recovery/simple-talk-sql-server-backup-crib-sheet/

A Recovery Process does NOT create Logs. Logs are what are required in order to perform a point in time recovery process (along with a full database backup, and possibly differential backups).

A database with 'bulk-logged' recovery will function much like a 'full' one except that only the effect of bulk operations are recorded in the backups, rather than the transactions involved. These bulk operations include BCP, certain Data Transformation Services (DTS) operations, image and text manipulations, and SELECT INTO. By using a 'bulk-logged' recovery model, the bulk operations are unlogged and therefore much quicker. Point-in-time recovery is not possible if one of these bulk operations have taken place because if a log backup covers any bulk operations, the log backup contains both log records and the data pages that were changed by bulk operations, and this applies to all changes up to the point that the log was backed up.

Mitch Wheat
Thanks Mitch!1. 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 od recovery process)?2. So, you think my understanding A is correct for bulk log recovery?
George2
If a system fails in the middle of a recovery, you are in the same position as at the start of the recovery. Just start again. A database is not usable until the end of the recovery process is indicated by activating the Database for normal use.
Mitch Wheat
But if recovery fails in the middle (suppose we restart the database server machine), then when database service is restarted, how could we know recovery status is failed in the middle and try to recover again? If we forget to recover and continue to use, database will be in inconsistent status?
George2
"if system fails in the middle of recovery" In this scenario database will be marked as "Suspect" and therefore not available for use. RESTORE again successfully and Suspect will be cleared. Sounds like you want a broader understanding, and its hard to teach you here, suggest tou read SQLServer DOCs
Kristen
Hi Kristen, I have learned a lot of topics in SQL Server 2005/2008 Books online. I have never learned topics like what will happen if recovery fails in the middle, the books are more about recovery modes and pros and cons. Do you have any good documents to recommend me to read?
George2
Hi Kristen, I have another throught and if recovery fails in the middle and the recovery package is also destroyed, then the database is totally useless?
George2
Certainly. You would revert to an earlier backup. Note that if your TLog backups are good, but your Full backup is bad, you can restore from an earlier Full and then EVERY Tlog backup since. Best Practice, as with all DBs, is to make Test Restore to ensure backup file is readable (c.f. Verify)
Kristen
Kristen is absolutely correct: you do not have a backup until you have tested that it can be restored to a test DB!
Mitch Wheat