Logging and Recovery in Visual Basic .NET

Generate UCC - 12 in Visual Basic .NET Logging and Recovery

4

After the differential backup is restored, you must restore, in sequence, all the log backups made after the differential backup was made. This includes the tail of the log backed up after the failure if you were able to make this last backup.

previously in this chapter, but it is a REDO-only operation. It includes an analysis pass to determine how much work might need to be done, and then a roll-forward pass to redo completed transactions and return the database to the state it was in when the backup was complete. Unlike restart restore recovery, you have control over when the rollback pass is done. It should not be done until all the rolling forward from all the backups has been applied. Once a RESTORE WITH RECOVERY is speci ed, after the redo pass, the database is restarted and SQL Server runs a restart recovery to undo incomplete transactions. In addition, SQL Server might need to make some adjustments to metadata after the recovery is complete, so no access to the database is allowed until all phases of recovery are nished. In other words, you don t have the option to use fast recovery as part of a RESTORE.

SQL Server 2008 allows you to back up individual les or legroups. This can be useful in environments with extremely large databases. You can choose to back up just one le or legroup each day, so the entire database does not have to be backed up as often. This also can be useful when you have an isolated media failure on a single drive and you think that restoring the entire database would take too long. Here are some details to keep in mind about backing up and restoring les and legroups:

Individual les and legroups with the read-write property can be backed up only when your database is in FULL or BULK_LOGGED recovery mode because you must apply log backups after you restore a le or legroup, and you can t make log backups in SIMPLE mode. Read-only legroups and the les in them can be backed up in SIMPLE mode. You can restore individual le or legroup backups from a full database backup. Immediately before restoring an individual le or legroup, you must back up the transaction log. You must have an unbroken chain of log backups from the time the le or legroup backup was made. After restoring a le or legroup backup, you must restore all the transaction logs made between the time you backed up the le or legroup and the time you restored it. This guarantees that the restored les are in sync with the rest of the database.

For example, suppose that you back up legroup FG1 at 10 A.M. on Monday. The database is still in use, and changes happen to data in FG1 and transactions are processed that change data in both FG1 and other legroups. You back up the log at 4 P.M. More transactions are processed that change data in both FG1 and other legroups. At 6 P.M., a media failure occurs and you lose one or more of the les that make up FG1.

To restore, you must rst back up the tail of the log containing all changes that occurred between 4 P.M. and 6 P.M. The tail of the log is backed up using the special WITH NO_TRUNCATE option, but you can also use the NORECOVERY option. When backing up the tail of the log WITH NORECOVERY, the database is put into the RESTORING state and can prevent an accidental background change from interfering with the restore sequence. You can then restore FG1 using the RESTORE DATABASE command, specifying just legroup FG1. Your database is not in a consistent state because the restored FG1 has changes only through 10 A.M., and the rest of the database has changes through 6 P.M. However, SQL Server knows when the last change was made to the database because each page in a database stores the LSN of the last log record that changed that page. When restoring a legroup, SQL Server makes a note of the maximum LSN in the database. You must restore log backups until the log reaches at least the maximum LSN in the database, and you do not reach that point until you apply the 6 P.M. log backup.