(Editor’s note: I just discovered that this blog did not post on 2013-Feb-06 as it was originally intended)

Yesterday, I was asked what the difference was in some of the restoration options.  Thus, I thought this would make a good blog post as it is somewhat confusing in the beginning.

On the options page of the restore database interface in SQL Server Management Studio, there are several options than can be quite frightening to new DBAs.  I have tried to explain them here in the order that they appear on the screen.

In the Recovery State options:

• Leave the database ready to use by rolling back uncommitted transactions.  Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY):  Choose this option only if you are restoring the log files now (in full or bulk-logged recovery models).  This is the default option and allows you to only go back to the last backup file.
  
• Leave the database non-operational, and do not roll back uncommitted transactions.  Additional transaction logs can be restored. (RESTORE WITH NORECOVERY):  Pick this option when you want to go to the very latest possible data!  Leaves the database in a restoring mode as you restore the backup and then all of the transaction logs generated since the backup up to the point of failure (if possible).
  
• Leave the database in read-only mode.  Undo committed transactions, but save the undo actions in a standby file so that recovery effects can be reversed. (RESTORE WITH STANDBY):  The database will be left in read-only mode.  At this point you can still apply transactions logs and is generally used when it takes too long to restore the system to a full database and you wish to use the server as a stand by server.
  

I hope this helps, enjoy!