Recovering and Restoring a SQL Server Database

By Mike Aubert, May 31st, 2002 Posted in SQL Server. Subscribe to our RSS Feed

So, that is good, but what the heck does recovering from an improper shut down have to do with a recovery of a backup? Everything…they are virtually the same process. If you remember back to Database Backups, I said that SQL Server makes “fuzzy backups,” because the backups are not from a single point in time. Due to the ability of database data modification during a backup, the backup is left in an inconsistent state - we can end up with half the data on our backup that is from before a large modification and the other half that is from after a large modification. To overcome this problem SQL Server can use the portion of the log file it captured during the backup to recover the data from the backup. After the data is restored from a backup, the recovery process can be used to roll forward all the transactions that took place during the backup - leaving the data in a consistent state.

There is one more important thing to know about restore recovery, once you recover a database you can’t apply any more transaction log backups. Due to the way log backups are made, it is possible to end up with part of a transaction on one log and the other half on the next log. If we ran the recovery process after applying the first log backup, SQL Server would see that we had a transaction that was only half done and therefore roll back the uncompleted transaction. Now, speaking theoretically here, if we could apply the next log backup what would happen during recovery? Because we have already run the recovery process after the first log backup, we would only have the second half of a transaction and SQL Server would have no clue what to do with it. Also, you can’t just skip this first transaction…later transactions may depend on this first modification. In order to overcome this problem and apply multiple backups, SQL Server gives us the option to run the recovery process or not. This allows you to restore the first log backup (which contains the first half of our example transaction) and then apply the second log backup (which contains the second half) before running the recovery process.

In summary, after restoring a backup if you choose to recover the database all completed transactions will be rolled forward, all uncompleted transactions will be rolled back, and the database will be accessible to users. If you choose not to recover the database after restoring a backup, the database will be left in an inconsistent state, it will not be accessible to users, however you will be able to restore more log backups.

Written by Mike Aubert - Visit Website

Go To Page: 1 2 3 4

All Tutorials by Category:

Entire site Copyright © 1999-2007 2000Trainers.com, all rights reserved.
Content on this site may not be copied or reproduced in any way without permission.

IT Showcase