Sunday, March 29, 2009

How to Back Up the Transaction Log When the Database Is Damaged

This is one of the best and most frequently asked question in the SQL DBA interview especially if you are being interview for "Production Support DBA" role.

"How to Back Up the Transaction Log When the Database Is Damaged" aka "how to perform tail log backup" - you will face this kind of issues when your database which get corrupted and goes in suspect most while in use or when you recover from a server crash mostly in cases like disk failures. When such failures occur your best method to recover the data is to restore your backup (full/diff/t.logs), so when do we need the tail log backups ?

Assume a case like this one - you take a full backup every night and differentials every 4 hrs. and t.log backups every 15 minutes, which most of us do. Suppose your t.log backup was about to start at 11:15 AM and crash happens at 11:13 AM. So, we are left with a gap of 11:00 AM to 11:13 AM transaction - how do we recover from this gap, yes we depend on tail log. There is no gurantee that you will recover best all of the data, but is a "best effort" strategy as a responsible DBA.

Here is the T-SQL command,


Notice the keyword "NO_TRUNCATE", why we need it ? - NO_TRUNCATE clause allows the active part of the t.log to be backed up even if the database is inaccessible/offline, provided the transaction log file is accessible and undamaged, this is very important so keep it in mind :) - for interview as well as for real case where you are required to recover the database from a failure.Why it's called tail log ? - because this is the last backup taken from the failed database in order to recover from failures.

No comments:

About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.