Today 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:

• Overwrite the existing database (WITH REPLACE): This is a scary option as you should always make sure that you REALLY want to overwrite an existing database.  Proceed with caution.  This option overwrites a safety check that prevents you from overwriting database X with database Y.
• Preserve the replication settings (WITH KEEP_REPLICATION): This option is really only relevant if the database was replicated when the backup was created.  I do not have a strong background in replication so I don’t have much to add to this item.
  
• Prompt before restoring each backup:  This essentially will let you pause a restore with a dialog box prompt when moving between media sets.  Unless your backup is stored across multiple tapes, this is probably of little use to you.  This might be helpful if you would like to break up the restore into smaller pieces, but I am not sure that is practical.
  
• Restrict access to the restored database (WITH RESTRICTED_USER): This option will make your restored database only available to the members of sysadmin, dbcreator, or db_owner roles.  This might be helpful if you need to perform additional steps before making the database available to the users to keep them from accidentally jeopardizing some scripts you need to run post restore.
  

I hope this helps your understanding.  Stay tuned for tomorrow where we discuss the recovery state options.  Enjoy!