August 3, 2010 at 7:27 am
I'm writing a restore scenario for different kinds of disaster scenario's that can happen to my databases. I'm having a bit of a problem with the scenario in which my transaction log disk crashes.
I'd like to loose as less data as possible.
Example:
- My last transaction backup is made at 2:00 PM
- My transaction log disk crashes at 3:00 PM
Is their a method to recover the database until 3:00 PM, or can it only be recovered until 2:00 PM with a restore until the last transaction log backup?
I'm testing this by just throwing away the .ldf file, while SQL is running, so the database isn't shutdown cleanly.
- I have tried detaching the database and attach it again (not possible because it isn't shutdown cleanly).
- I have tried attach it again through a create database statement with attach_rebuild_log.
- I have tried to make a full backup (with copy_only), but that doesn't work, because it needs the log.
- I have tried DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS (and it did loose data).
btw. I know the transaction log should be on a mirrored drive.
Regards,
Marco
August 3, 2010 at 7:39 am
mchofman (8/3/2010)
Example:- My last transaction backup is made at 2:00 PM
- My transaction log disk crashes at 3:00 PM
Is their a method to recover the database until 3:00 PM, or can it only be recovered until 2:00 PM with a restore until the last transaction log backup?
The latter. That's why the interval between transaction log backups should be the max allowable data loss for the system.
...
- I have tried DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS (and it did loose data).
Yes it will. Sometime it'll fail and you won't be able to recover the DB at all, depending on what was in-progress at the time the drive failed.
The proper solution in this scenario is 'restore from backup'. The emergency mode repairs are a LAST RESORT when there's no backup and no other possible solution. It can leave the DB transactionally inconsistent, it can leave the database structurally inconsistent and irreparable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 3, 2010 at 8:23 am
Don't run repair allow data loss.
http://www.sqlskills.com/BLOGS/PAUL/category/DBCC.aspx#p7
http://www.sqlskills.com/blogs/paul/post/Corruption-Last-resorts-that-people-try-first.aspx
If the t-log is gone, then you can't recover past your last log backup. If the disk with the t-log crashed, but the data mdf/ndf files are intact, you ought to be able to detach and attach the MDF, rebuild a new log, and take a full backup immediately.
August 3, 2010 at 8:27 am
Steve Jones - Editor (8/3/2010)
If the disk with the t-log crashed, but the data mdf/ndf files are intact, you ought to be able to detach and attach the MDF, rebuild a new log, and take a full backup immediately.
Not necessarily. Only if the database had been cleanly shut down at the point the log file was lost. In the case of an unexpected drive failure, that's not too likely.
If the log file was lost while the database was in use, detach will succeed, attach will fail. The only way then to get the DB back (other than restore) is to hack it back in to the server and run an emergency mode repair ie repair_allow_data_loss.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 3, 2010 at 1:07 pm
Gail, Steve,
thanks for the replies. I always thought the data file was the most vulnerable file, but that can be restored until the time of the crash with a tail log backup (when the database is in full recovery mode).
I found this in the Books Online about the transaction log.
SQL Server 2005 uses a write-ahead log (WAL), which guarantees that no data modifications are written to disk before the associated log record is written to disk. This maintains the ACID properties for a transaction. For more information about transactions and ACID properties, see Transactions (Database Engine).
To understand how the write-ahead log works, it is important for you to know how modified data is written to disk. SQL Server maintains a buffer cache into which it reads data pages when data must be retrieved. Data modifications are not made directly to disk, but are made to the copy of the page in the buffer cache. The modification is not written to disk until a checkpoint occurs in the database, or the modification must be written to disk so the buffer can be used to hold a new page. Writing a modified data page from the buffer cache to disk is called flushing the page. A page modified in the cache, but not yet written to disk, is called a dirty page.
At the time a modification is made to a page in the buffer, a log record is built in the log cache that records the modification. This log record must be written to disk before the associated dirty page is flushed from the buffer cache to disk. If the dirty page is flushed before the log record is written, the dirty page creates a modification on the disk that cannot be rolled back if the server fails before the log record is written to disk. SQL Server has logic that prevents a dirty page from being flushed before the associated log record is written. Log records are written to disk when the transactions are committed.
The database can only be attached without a log, when it had a clean shutdown, so that a checkpoint has been made. Then it will create a new transaction log file. If it didn't have a clean shutdown, then a restore is the only way to have a guaranteed consistent database (like Gail explained).
I'm glad that I'm writing and testing different restore scenarios. I didn't expect the behaviour above, I thought data was immediately written to disk.
So this means transactions logs on fault-tolerant storage, such as mirrored disks and frequent LOG backups.
Regards and thanks,
Marco
August 3, 2010 at 1:23 pm
Good reference, and I hadn't realized a clean shutdown is needed. That's good to know.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply