Restoration Options

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!

Advertisements

About SQLGator

Florida Gator and Microsoft Data Platform MVP, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server DBA who dabbles with VMWare, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on February 5, 2013, in Maintenance and tagged . Bookmark the permalink. 1 Comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: