Restoring WITH RECOVERY and WITH NORECOVERY
The RESTORE DATABASE command does two things: It copies all the data, log, and index pages from the backup media to the database files, and it applies all the transactions in the backed-up portion of the log. You must determine whether to tell SQL Server to roll back incomplete transactions. If you want rollback, you can use the WITH RECOVERY option of the RESTORE DATABASE command to recover the database. The WITH RECOVERY option rolls back incomplete transactions and opens the database for use. If you plan to restore subsequent transaction log backups, and you don't want to recover the database and have SQL Server perform rollbacks until after the last transaction log is restored, you won't need to use the WITH RECOVERY option. The RESTORE LOG command also lets you specify either WITH RECOVERY or WITH NORECOVERY.
Remember that SQL Server 2000 and 7.0 log backups don't overlap—each log backup starts where the previous one ended. Consider a transaction that makes hundreds of updates to one table. If you back up the log in the middle of the updating and again after the updating is finished, the first log backup will include the beginning of the transaction and some of the updates, and the second log backup will include the remainder of the updates and the commit. Suppose you then need to restore these log backups after restoring the full database. If you choose to restore the first log backup WITH RECOVERY, SQL Server will roll back the incomplete transaction in the first part of the log. If you then try to restore the second log backup, the restore will start in the middle of a transaction and SQL Server won't know what the beginning of the transaction did. You can't recover transactions that occurred after this large update because their operations might depend on part of the update that you lost. So, SQL Server won't let you do any more restoring. The alternative is to run WITH NORECOVERY, which leaves the transaction incomplete. SQL Server will know that the database is inconsistent and won't let any users into the database until you run recovery on it.
So, should you choose WITH RECOVERY or WITH NORECOVERY? If you use the RESTORE command to restore a database or log backup, the default restore option is WITH RECOVERY. But generally, you should use the WITH NORECOVERY option for all but the last log restore. If you make a mistake and forget to specify WITH NORECOVERY, you'll have to restart your restore operation because the database has now been recovered and incomplete transactions have been rolled back. However, if you forget to specify WITH RECOVERY for the last log restore, the fix is simple. Just use the command below to recover the database without specifying any backup device to restore from:
RESTORE DATABASE <database name> WITH RECOVERY
For more information about recovery scenarios that use different combinations of differential and log backups, see Michael D. Reilly, Certifiably SQL, "Backup Strategies," http://www.sqlmag.com, InstantDoc ID 9629, and Certifiably SQL, "Restoring Databases," http://www.sqlmag.com, InstantDoc ID 9808.
Incomplete Restore
An incomplete restore is useful if someone accidentally destroys crucial data with an UPDATE or DELETE command and you want to just restore the database to the point before the data was destroyed. If you follow the usual restore procedure and apply the entire last transaction log, you'll reapply the transaction that damaged your database. But SQL Server 2000 and 7.0 let you restore your database up to a specific point in time.
The STOPAT option of the RESTORE command lets you specify a point at which to stop restoring a transaction log. Because each log record includes a datetime value that shows when the transaction began, SQL Server will stop restoring transactions as soon as it encounters a transaction that happened after the specified STOPAT time. If the transaction log backup doesn't contain the requested time (e.g., the time specified is after the time that the transaction log covers), SQL Server generates a warning and the database remains unrecovered, as if you'd run RESTORE WITH NORECOVERY.
In SQL Server 7.0, you can use the STOPAT option only with the RESTORE LOG command. In SQL Server 2000, you can also use STOPAT with the RESTORE DATABASE command, and STOPAT includes log records applied after all the database pages were loaded. However, you can't use STOPAT when you're restoring differential backups because most of the work of a differential restore is replacing data that has changed and no datetime value is associated with the changed pages. In addition, you can't use STOPAT with a file or filegroup restore because all the logs must be applied completely so that the restored file or filegroup is current through the same point in time as the rest of the files in the database.
Prev. page
1
2
[3]
4
next page 
