November 13, 2007 at 6:11 pm
Based on my understanding transaction undo file contains all the modifications on the database that is not yet committed?? (Correct me if im wrong) So it means that it contains all the data prior to the finished transactional backup?? then incase production server fails the TUF has all the data up to the failure of the production server. If yes then why do we need to still check if we can make a last backup on the production server if the TUF file in the secondary server has all the data we need for the recovery of the production server??
"-=Still Learning=-"
Lester Policarpio
November 13, 2007 at 7:27 pm
Your understanding is close. The txn undo file contains modifications that were not committed on the source database but were in progress when the txn log was backed up AND when the log was restored to another database, you left the database in a state that allowed addition txn log backups to be restored to it (at some point in the future. When another txn log is restored, SQL Server uses data from the undo file and the transaction log to continue restoring the incomplete transactions (assuming that they are were completed in the next txn log file). Following the restore, the undo file will be re-written with any transactions that, at that point, are incomplete.
SQL Server will not allow you to restore any incomplete/uncommitted transactions - in any circumstances. The undo file is only valid to use when restore multiple txn log files using separate restore commands.
If you have a production failure, the ONLY useful way to use the data that logically is in the TUF file is to use the restore command to restore each of the txn log backups.
In a disaster situation, you need to backup the txn log (if you can) because that is the only "place" where the most recent transactions are actually committed to. You will then need to use that txn log backup on your standby server to restore to the most recent completed transaction.
November 13, 2007 at 8:20 pm
Wow very informative thanks..
Some clarifications..
1. In cases where in production server fails and i can make a last tlog backup from it you are saying that i must apply first the TUF before the last transaction log backup to the standby server? Correct?? so i can attain the latest tlog from the production server.
2. I cannot make the TUF as my last tlog backup because SQL Server doesn't allow us to restore any incomplete/uncommitted transactions. And TUF stores uncommitted transactions?? right??
3. Given this scenario: tlog backup every 15 min. lets say nov. 14,2007 i have
11:00AM tlog backup
11:15AM tlog backup
11:30 AM tlog backup - up to this tlog is already loaded in 2ndary server
11:45AM tlog backup
12:00NN TUF - because time now is 12:03 PM
then the server crashes (for whatever the reason) and i cant make any last tlog backup to the production server.
Conclusion : I can only restore up to 11:45AM since 11:30AM is already loaded to the secondary server and i cannot apply the 12:00NN TUF since i cant make anymore tlog backups in the production. I will loose 18 min worth of transactions.
But if i can make a last tlog backup from the production server then apply first the TUF (please clear this) and last is the last tlog backup from the production server. Thank you very much in advance....
"-=Still Learning=-"
Lester Policarpio
November 13, 2007 at 8:42 pm
1. You must always have a transaction log backup file so that you can roll forward. When you do a restore, the uncommitted transactions that are in undo file are combined with the transaction log backup file so that all transactions that were committed in the transaction log backup file are applied to the restored database. You will need the latest transaction log backup file from the prod server.
2. You cannot in any circumstances use the undo file without a related transaction log backup file. The undo file only stores uncommitted transactions. A restore operation will only write committed transactions to your database.
3. You should be able to recover to, at least, 12:00 - assuming the this transaction log backup completed and is accessible. If you are able to access the transaction log file with a BACKUP LOG command you may also be able to recover to 12:03 - this really depends on how badly things went wrong.
Have a read of the topics in Book Online that discuss backup and recovery. They should give you all the info you need. One that is useful is "How to set up, maintain, and bring online a standby server (Transact-SQL)".
November 14, 2007 at 1:43 am
Thank you very much very well said....
"-=Still Learning=-"
Lester Policarpio
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply