February 8, 2006 at 5:29 am
In backup texts, we often come across the terms "restoring" and "recovering." Looks like these two words are synonymous, but then the books emphasize that they are different. Could someone please clarify the difference between the two and identify contexts in which one might be more suitable than the other.
Thanks,
Karim
February 8, 2006 at 6:35 am
Karim,
This topic may be a little subjective so I'll tell how I would clarify the two. Recovery is the process required to bring your production database back online. Restoring a database is one step in your recovery process. Recovery is an all inclusive process and not just the restore step. For examle, in order to recover your production database, you may also have to recover your disk if one of them has gone bad. Recovery could include things like making sure you are up to date on Service Packs. This is how I look at it.
HTH,
Mark
February 8, 2006 at 8:04 am
Mark has a good point. In fact the RESTORE DATABASE command uses the WITH RECOVERY or WITH NORECOVERY option (default is WITH RECOVERY). So, it is possible to RESTORE a database and not RECOVER it. If it is not recovered, then it is not in a 'usable' mode.
-SQLBill
February 9, 2006 at 2:47 pm
Restore and recovery are two distinct operations. Here's an explanation.
During normal operations, any database (SQL Server, Oracle, DB/2,...) will have multiple concurrent users working and making changes to the database. In some cases, you will have large transactions going on--by default using DTS to load a million records into a table is a single large transaction. The DBMS normally stores everything in pages that are the same size, where pages may contain multiple objects and some database objects span multiple pages. Further, pages are read into buffers in a large buffer pool, modified by multiple independent operations, and are written back to disk independent of any operations.
To give the appearance of order, and to keep things straight, all DBMS keep some persistent record of transactions and the operations that take place within them. For SQL Server, this is the Transaction Log (T-Log). When you start a transaction and modify the database, SQL Server creates T-Log entries. When you commit or rollback a transaction, SQL Server actualy just guarantees that the T-Log entries have been recorded to disk, so it will not forget what you have done. It doesn't guarantee that your work has been saved to disk, just that it can recreate it when needed.
So the database, on disk and in the buffer pool, is normally in an inconsistent and "dirty" state. This is needed to quickly and efficiently handle all the concurrent users. The DBMS just presents each user with the illusion that they are the only customer.
A backup operation copies the database, in its inconsistent state, to the backup file. For full and differential backups, the data pages are copied from disk and/or buffer pool. For a log backup, it is copying data directly from the T-Log to the backup file. It does not try to create an internally consistent "point-in-time" view of the database.
A restore operation read the backup file sequentially from beginning to end, and copies the information into the database (disk and buffer pool). For a RESTORE LOG, it performs the appropriate operation that created the T-Log entry. The result is an inconsistent and "dirty" database.
A recovery operation takes a database in its "dirty" state, and uses T-Log entries to clean it up into a clean ad consistent state. It makes sure that the disk (and buffer pool) reflects all commited transactions. The effects of any incomplete or rolled back transactions are removed.
SQL Server does a recovery operation during a normal shutdown, or when a database is detached or made read only. In case things dd not end cleanly, a recovery is also done when SQL Server starts up or a database is attached.
The confusion comes because of a matter of convenience. We want a full or differential restore to produce a clean database. So, after all the data files have been copied to the backup file, SQL Server copies the active part of the T-Log into the backup file as well. By default, the RESTORE command will perform a restore operation using the saved database pages, and then do a recovery operation using the saved T-Log entries.
I hope this clarifies the difference between restoring and recovery.
David Lathrop
DBA
WA Dept of Health
February 9, 2006 at 9:30 pm
Your points really clarify the issue at hand. Thanks a lot.
Karim
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply